---

Search posts under SQL Server:

---

Retrieve data in XML format in SQL Server

How to retrieve data in XML format from SQL Server?

To retrieve data in XML format from SQL Server database, we can use FOR XML <options> clause.

SELECT * FROM PersonalDetails FOR XML RAW

Notice the last three words in the above query. We have a normal SELECT statement and in the last of the statement, we are suffixing FOR XML RAW that will return the data from PersonalDetails table in Raw xml format.

The XML data

<row FirstName="Sheo" LastName="Narayan" Age="30" Active="1" PersonalDetailsId="1"
/>
<row FirstName="Sunita" LastName="Narayan" Age="25" Active="1"
PersonalDetailsId="2" />
<row FirstName="Sindhuja" LastName="Narayan" Age="8" Active="0"
PersonalDetailsId="3" />
<row FirstName="Shreeharsh" LastName="Narayan" Age="3" Active="1"
PersonalDetailsId="4" />
<row FirstName="Sambhu" LastName="Singh" Age="9" Active="1" PersonalDetailsId="5"
/>
<row FirstName="Sheo P" LastName="Singh" Age="35" Active="1" PersonalDetailsId="9"
/>
<row FirstName="Ram" LastName="Pawar" Age="20" Active="0" PersonalDetailsId="11"
/>
<row FirstName="Harish" LastName="Arun" Age="42" Active="1" PersonalDetailsId="31"
/>

Notice the node name here, it is row that simply represent each row of the database table.

 When we change the option to AUTO

SELECT * FROM PersonalDetails FOR XML AUTO

The same query returns almost same XML as in the previous case however the XML node name changes to the database table name.

<PersonalDetails FirstName="Sheo" LastName="Narayan" Age="30" Active="1"
PersonalDetailsId="1" />
<PersonalDetails FirstName="Sunita" LastName="Narayan" Age="25" Active="1"
PersonalDetailsId="2" />
<PersonalDetails FirstName="Sindhuja" LastName="Narayan" Age="8" Active="0"
PersonalDetailsId="3" />
<PersonalDetails FirstName="Shreeharsh" LastName="Narayan" Age="3" Active="1"
PersonalDetailsId="4" />
<PersonalDetails FirstName="Sambhu" LastName="Singh" Age="9" Active="1"
PersonalDetailsId="5" />
<PersonalDetails FirstName="Sheo P" LastName="Singh" Age="35" Active="1"
PersonalDetailsId="9" />
<PersonalDetails FirstName="Ram" LastName="Pawar" Age="20" Active="0"
PersonalDetailsId="11" />
<PersonalDetails FirstName="Harish" LastName="Arun" Age="42" Active="1"
PersonalDetailsId="31" />

Notice the node name “PersonalDetails” ie. the name of the database table

In case, we want a custom parent node and child node of the XML, we can specify PATH and ROOT parameter to the last like this

SELECT * FROM PersonalDetails FOR XML PATH('PersonalDetail'),
ROOT('PersonalDetails')

Here, we have specified PATH parameter value as “PersonalDetial” and ROOT as “PersonalDetials” and the same will get applied in the resultant XML.

We can specify any string with PATH and ROOT, not necessarily in line with the database table names.

<PersonalDetails>
  <PersonalDetail>
    <FirstName>Sheo</FirstName>
    <LastName>Narayan</LastName>
    <Age>30</Age>
    <Active>1</Active>
    <PersonalDetailsId>1</PersonalDetailsId>
  </PersonalDetail>
  <PersonalDetail>
    <FirstName>Sunita</FirstName>
    <LastName>Narayan</LastName>
    <Age>25</Age>
    <Active>1</Active>
    <PersonalDetailsId>2</PersonalDetailsId>
  </PersonalDetail>
<PersonalDetail>
  <FirstName>Sindhuja</FirstName>
  <LastName>Narayan</LastName>
  <Age>8</Age>
  <Active>0</Active>
  <PersonalDetailsId>3</PersonalDetailsId>
</PersonalDetail>
<PersonalDetail>
  <FirstName>Shreeharsh</FirstName>
  <LastName>Narayan</LastName>
  <Age>3</Age>
  <Active>1</Active>
  <PersonalDetailsId>4</PersonalDetailsId>
</PersonalDetail>
<PersonalDetail>
  <FirstName>Sambhu</FirstName>
  <LastName>Singh</LastName>
  <Age>9</Age>
  <Active>1</Active>
  <PersonalDetailsId>5</PersonalDetailsId>
</PersonalDetail>
<PersonalDetail>
  <FirstName>Sheo P</FirstName>
  <LastName>Singh</LastName>
  <Age>35</Age>
  <Active>1</Active>
  <PersonalDetailsId>9</PersonalDetailsId>
</PersonalDetail>
<PersonalDetail>
  <FirstName>Ram</FirstName>
  <LastName>Pawar</LastName>
  <Age>20</Age>
  <Active>0</Active>
  <PersonalDetailsId>11</PersonalDetailsId>
</PersonalDetail>
<PersonalDetail>
  <FirstName>Harish</FirstName>
  <LastName>Arun</LastName>
  <Age>42</Age>
  <Active>1</Active>
  <PersonalDetailsId>31</PersonalDetailsId>
</PersonalDetail>
</PersonalDetails>

Saving as XML file in SQL Server

Let's say we have a scenario where we want to save the xml content as file then we can alter the code a bit.

Look at below code snippet. Here we have declared a xml variable and set it's value to the query result of the PersonalDetails table (query that returns xml content).

DECLARE @xmlContent as xml
SET @xmlContent = (
                        SELECT * FROM PersonalDetails FOR 
                        XML PATH('PersonalDetail'), ROOT('PersonalDetails')
                  )
SELECT @xmlContent

The result window would look like this.

Now here we can do 2 things

1. Click (left) on the cell that contains hyperlink of the xml content and it will get converted to xml file as shown below or
2. Right click the results cell and select 'Save Restult As..." that opens Save dialog box. Give appropriate name after selecting the "Save as type" from the dropdown and your xml content will be saved as file.

Cool, if you liked this do share with your friends!