SQL - XML RAW

USE AdventureWorks2008R2

GO

-- Use RAW modal to create XML

SELECT p.FirstName AS firstName,

    p.MiddleName AS middleName,

    p.LastName AS lastName

FROM Person.Person AS p

FOR XML RAW

/** -- Output --

<row firstName="Syed" middleName="E" lastName="Abbas" />

<row firstName="Catherine" middleName="R." lastName="Abel" />

<row firstName="Kim" lastName="Abercrombie" />

<row firstName="Kim" lastName="Abercrombie" />

<row firstName="Kim" middleName="B" lastName="Abercrombie" />

<row firstName="Hazem" middleName="E" lastName="Abolrous" />

**/

-- Use RAW modal and set the ROOT for XML elements

SELECT p.FirstName,

    p.MiddleName,

    p.LastName

FROM Person.Person AS p

FOR XML RAW('Person'),

   ROOT('Persons')

/** -- Output --

<Persons>

  <Person FirstName="Syed" MiddleName="E" LastName="Abbas" />

  <Person FirstName="Catherine" MiddleName="R." LastName="Abel" />

  <Person FirstName="Kim" LastName="Abercrombie" />

  <Person FirstName="Kim" LastName="Abercrombie" />

  <Person FirstName="Kim" MiddleName="B" LastName="Abercrombie" />

  <Person FirstName="Hazem" MiddleName="E" LastName="Abolrous" />

  <Person FirstName="Sam" LastName="Abolrous" />

</Persons>

**/

-- Use RAW modal with ELEMENTS and XSINIL

-- Notice the Person who doesn't have the Middle Name 

SELECT p.FirstName,

    p.MiddleName,

    p.LastName

FROM Person.Person AS p

FOR XML RAW('Person'),

   ROOT('Persons'),

   ELEMENTS XSINIL

/** -- Output --

<Persons xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Person>

    <FirstName>Syed</FirstName>

    <MiddleName>E</MiddleName>

    <LastName>Abbas</LastName>

  </Person>

  <Person>

    <FirstName>Catherine</FirstName>

    <MiddleName>R.</MiddleName>

    <LastName>Abel</LastName>

  </Person>

  <Person>

    <FirstName>Kim</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Abercrombie</LastName>

  </Person>

</Persons>

**/

-- Use RAW modal with ELEMENTS 

-- Notice the Person who doesn't have the Middle Name 

SELECT p.FirstName,

    p.MiddleName,

    p.LastName

FROM Person.Person AS p

FOR XML RAW('Person'),

   ROOT('Persons'),

   ELEMENTS  

/** -- Output --

<Persons>

  <Person>

    <FirstName>Syed</FirstName>

    <MiddleName>E</MiddleName>

    <LastName>Abbas</LastName>

  </Person>

  <Person>

    <FirstName>Catherine</FirstName>

    <MiddleName>R.</MiddleName>

    <LastName>Abel</LastName>

  </Person>

  <Person>

    <FirstName>Kim</FirstName>

    <LastName>Abercrombie</LastName>

  </Person>

</Persons>

**/