Sunday, April 12, 2015

Different XML output format for “XML RAW”, “XML AUTO” and “ELEMENTS”

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML RAW

<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML RAW ,ELEMENTS

<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>43665</SalesOrderID>
  <OrderDate>2005-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>44284</SalesOrderID>
  <OrderDate>2005-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45043</SalesOrderID>
  <OrderDate>2006-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45782</SalesOrderID>
  <OrderDate>2006-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>46611</SalesOrderID>
  <OrderDate>2006-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>47666</SalesOrderID>
  <OrderDate>2006-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>48757</SalesOrderID>
  <OrderDate>2007-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>49826</SalesOrderID>
  <OrderDate>2007-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>51089</SalesOrderID>
  <OrderDate>2007-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>55241</SalesOrderID>
  <OrderDate>2007-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>61182</SalesOrderID>
  <OrderDate>2008-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>67266</SalesOrderID>
  <OrderDate>2008-04-01T00:00:00</OrderDate>
</row>

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML AUTO

<C CustomerID="29580" AccountNumber="AW00029580">
  <Soh SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
  <Soh SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
  <Soh SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
  <Soh SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
  <Soh SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
  <Soh SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
  <Soh SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
  <Soh SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
  <Soh SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
  <Soh SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
  <Soh SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
  <Soh SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />
</C>

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML AUTO ,ELEMENTS

<C>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <Soh>
    <SalesOrderID>43665</SalesOrderID>
    <OrderDate>2005-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>44284</SalesOrderID>
    <OrderDate>2005-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45043</SalesOrderID>
    <OrderDate>2006-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45782</SalesOrderID>
    <OrderDate>2006-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>46611</SalesOrderID>
    <OrderDate>2006-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>47666</SalesOrderID>
    <OrderDate>2006-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>48757</SalesOrderID>
    <OrderDate>2007-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>49826</SalesOrderID>
    <OrderDate>2007-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>51089</SalesOrderID>
    <OrderDate>2007-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>55241</SalesOrderID>
    <OrderDate>2007-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>61182</SalesOrderID>
    <OrderDate>2008-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>67266</SalesOrderID>
    <OrderDate>2008-04-01T00:00:00</OrderDate>
  </Soh>
</C>