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 RAWNotice 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.
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 AUTOThe same query returns almost same XML as in the previous case however the XML node name changes to the database table 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.
1Kisan8889177 2Ravi8992181