FOR XML Path ( SQL Server )

For xml path gives you flexibility to generate xml from your database in the desired structure with less code .
Believe me once you learn the xml path you will love this feature .

So lets start with a simple customer  table , the schema of the table is as follows :

If if just write for xml auto query like this :

select * from dbo.Customer for xml auto

, we will get the xml like this :

<dbo.Customer CustomerId="1" FirstName="Priyanka" LastName="Srivastava" Address="lucknow" Email="priyanka@yahoo.com" PhoneNumber="23232232" />

<dbo.Customer CustomerId="2" FirstName="Manish " LastName="Sharma" Address="mumbai" Email="manish@gmail.com" PhoneNumber="232323" />

<dbo.Customer CustomerId="3" FirstName="Kanak" LastName="Srivastava" Address="Delhi" Email="kanak@hotmail.com" PhoneNumber="2165273" />

<dbo.Customer CustomerId="4" FirstName="Rachita" LastName="Pandey" Address="Bhopal" Email="Rachita@abc.com" PhoneNumber="7868768" />

<dbo.Customer CustomerId="5" FirstName="Jitender" LastName="Singh" Address="Patiala" Email="Jitender@abc.com" PhoneNumber="897879" />

Now if you want your xml to be structured in very customized way , we can use for xml path . in this we have option to choose whether we want to show a particular column as an attribute or an element .

To display a column as an attribute add <‘@’> before alias name and to make an element just write the alias name .suppose you want to get every customer record with a Customer tag and first name with FisrtName attribute and last name with LastName Attribute.

SELECT CustomerId AS '@Id',

FirstName AS '@FirstName',

LastName AS '@LastName'

FROM dbo.Customer FOR XML PATH('Customer')

Resultant XML will be :

<Customer Id="1" FirstName="Priyanka" LastName="Srivastava" />

<Customer Id="2" FirstName="Manish " LastName="Sharma" />

<Customer Id="3" FirstName="Kanak" LastName="Srivastava" />

<Customer Id="4" FirstName="Rachita" LastName="Pandey" />

<Customer Id="5" FirstName="Jitender" LastName="Singh" />

We can do any kind of manipulation in the select phrase and give it as an attribute of element of the xml :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name'

FROM dbo.Customer FOR XML PATH('Customer')

Resultant XML will be :

<Customer Id="1" Name="Priyanka Srivastava" />

<Customer Id="2" Name="Manish Sharma" />

<Customer Id="3" Name="Kanak Srivastava" />

<Customer Id="4" Name="Rachita Pandey" />

<Customer Id="5" Name="Jitender Singh" />

To provide a root element of the table rows we need to use ROOT keyword

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

<MyCustomers>

  <Customer Id="1" Name="Priyanka Srivastava" />

  <Customer Id="2" Name="Manish Sharma" />

  <Customer Id="3" Name="Kanak Srivastava" />

  <Customer Id="4" Name="Rachita Pandey" />

  <Customer Id="5" Name="Jitender Singh" />

</MyCustomers>

Now suppose we need the Contact details as child element in the customer element , and within the Contact details we need three different attributes for address , email and phone number :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name',

[Address] AS 'ContactDetails/@PostalAddress',

Email AS 'ContactDetails/@EmailAddress',

PhoneNumber AS 'ContactDetails/@PhoneNumber'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

<MyCustomers>

  <Customer Id="1" Name="Priyanka Srivastava">

    <ContactDetails PostalAddress="lucknow" EmailAddress="priyanka@yahoo.com" PhoneNumber="23232232" />

  </Customer>

  <Customer Id="2" Name="Manish Sharma">

    <ContactDetails PostalAddress="mumbai" EmailAddress="manish@gmail.com" PhoneNumber="232323" />

  </Customer>

  <Customer Id="3" Name="Kanak Srivastava">

    <ContactDetails PostalAddress="Delhi" EmailAddress="kanak@hotmail.com" PhoneNumber="2165273" />

  </Customer>

  <Customer Id="4" Name="Rachita Pandey">

    <ContactDetails PostalAddress="Bhopal" EmailAddress="Rachita@abc.com" PhoneNumber="7868768" />

  </Customer>

  <Customer Id="5" Name="Jitender Singh">

    <ContactDetails PostalAddress="Patiala" EmailAddress="Jitender@abc.com" PhoneNumber="897879" />

  </Customer>

</MyCustomers>

Now if we want the address , phone and email should be elements within the customer tag :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name',

[Address] AS 'ContactDetails/PostalAddress',

Email AS 'ContactDetails/EmailAddress',

PhoneNumber AS 'ContactDetails/PhoneNumber'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

<MyCustomers>

  <Customer Id="1" Name="Priyanka Srivastava">

    <ContactDetails>

      <PostalAddress>lucknow</PostalAddress>

      <EmailAddress>priyanka@yahoo.com</EmailAddress>

      <PhoneNumber>23232232</PhoneNumber>

    </ContactDetails>

  </Customer>

  <Customer Id="2" Name="Manish Sharma">

    <ContactDetails>

      <PostalAddress>mumbai</PostalAddress>

      <EmailAddress>manish@gmail.com</EmailAddress>

      <PhoneNumber>232323</PhoneNumber>

    </ContactDetails>

  </Customer>

  <Customer Id="3" Name="Kanak Srivastava">

    <ContactDetails>

      <PostalAddress>Delhi</PostalAddress>

      <EmailAddress>kanak@hotmail.com</EmailAddress>

      <PhoneNumber>2165273</PhoneNumber>

    </ContactDetails>

  </Customer>

  <Customer Id="4" Name="Rachita Pandey">

    <ContactDetails>

      <PostalAddress>Bhopal</PostalAddress>

      <EmailAddress>Rachita@abc.com</EmailAddress>

      <PhoneNumber>7868768</PhoneNumber>

    </ContactDetails>

  </Customer>

  <Customer Id="5" Name="Jitender Singh">

    <ContactDetails>

      <PostalAddress>Patiala</PostalAddress>

      <EmailAddress>Jitender@abc.com</EmailAddress>

      <PhoneNumber>897879</PhoneNumber>

    </ContactDetails>

  </Customer>

</MyCustomers>

Joining multiple tables also work in same way u just need to understand the XML structure and apply elements or tags within the select clause .

Hope this will be helpful to start with the XML path . Actually you can do much more than this using this powerful SQL Feature :)

Let me know if you are facing any issue in implementing this in comment section of this blog .

Cheers

Priyanka