SQL - XML Auto

USE AdventureWorks2008R2

GO

-- Auto with ELEMENTS

SELECT p.BusinessEntityID,

       p.FirstName,

       p.MiddleName,

       p.LastName

FROM Person.Person AS p -- The element name will be same with 'p'

FOR XML AUTO,

ROOT('Persons'),

ELEMENTS XSINIL

/** -- Output --

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

   <p>

    <BusinessEntityID>285</BusinessEntityID>

    <FirstName>Syed</FirstName>

    <MiddleName>E</MiddleName>

    <LastName>Abbas</LastName>

  </p>

  <p>

    <BusinessEntityID>293</BusinessEntityID>

    <FirstName>Catherine</FirstName>

    <MiddleName>R.</MiddleName>

    <LastName>Abel</LastName>

  </p>

  <p>

    <BusinessEntityID>295</BusinessEntityID>

    <FirstName>Kim</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Abercrombie</LastName>

  </p>

</Persons>

**/

-- Auto ELEMENTS

SELECT person.BusinessEntityID AS ID,

       person.FirstName        AS firstName,

       person.MiddleName       AS middleName,

       person.LastName         AS lastName

FROM Person.Person             AS person  -- Row Name

FOR XML AUTO,

ROOT('Persons'),

ELEMENTS XSINIL

/** -- Output -- Notice the output element name

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

  <person>

    <ID>285</ID>

    <firstName>Syed</firstName>

    <middleName>E</middleName>

    <lastName>Abbas</lastName>

  </person>

  <person>

    <ID>293</ID>

    <firstName>Catherine</firstName>

    <middleName>R.</middleName>

    <lastName>Abel</lastName>

  </person>

  <person>

    <ID>295</ID>

    <firstName>Kim</firstName>

    <middleName xsi:nil="true" />

    <lastName>Abercrombie</lastName>

  </person>

</Persons>

**/