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>
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
Cool, if you liked this do share with your friends!
Views: 23040 | Post Order: 108