How to Get data in XML format from SQL Server?

Updated on     Kisan Patel

This tutorial will explain you how to get data in XML format from SQL server database. To get data in XML format, we nee to use FOR XML <Option> clause.

SELECT Id, Name, Mark1, Mark2, Total FROM Student 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 Student table in Raw xml format.

sql-server-xml-data

 

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 Id, Name, Mark1, Mark2, Total FROM Student 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.

sql-server-xml-data-node-name

 

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 Id, Name, Mark1, Mark2, Total FROM Student FOR XML PATH('Student'), ROOT('Students')

Here, we have specified PATH parameter value as “Student” and ROOT as “Students” and the same will get applied in the resultant XML. Here, We can specify any string with PATH and ROOT, not necessarily in line with the database table names.

sql-server-xml-data-custom-node-name

 

1Kisan8889177 2Ravi8992181

SQL Server

Leave a Reply