Wednesday, May 18, 2011

Returning XML from SQL Server

SQL Server is a relational structure and XML is a hierarchy data structure. Hierarchal data is much more organized and Relational data is not organized in hierarchy way. With the release of SQL Server 2000, Microsoft introduced the capability of formatting return data streams from SQL Server as XML.

SQL Server supports this capability through a Transact-SQL (TSQL) clause called 'FOR XML'. This clause provides instructions to the data engine to output the results of the query in an XML format. Using various options, you have significant control over the format of that output, which gives you the ability to structure the data in a reasonable manner that the developer can then transform using Extensible Stylesheet Language (XSL) if desired.

Example:

SELECT Name, RollNo from Student.student
for XML RAW;

A RAW mode query returns each row of data in the result set as an element with the generic
label . If you look at the results, you will see that each row element represents a single row in the result set.

Following is an example of the output returned:

<row Name=”Ashwani” RollNo=”1700101”/>
<row Name=”Deepak” RollNo=”1700102”/>
<row Name=”Rajneesh” RollNo=”1700103”/>

We can add root element to make it well-formed.

Example:

SELECT Name, RollNo from Student.student
for XML RAW('Student'),
ROOT('Students')
ELEMENTS XSINIL;

This query returns a well-formed XML.

Following is an example of the output returned:

</Students>
<Student>
<Name>Ashwani</Name>
<RollNo>1700101</RollNo>
</Student>
<Student>
<Name>Deepak</Name>
<RollNo>1700102</RollNo>
</Student>
<Student>
<Name>Rajneesh</Name>
<RollNo>1700103</RollNo>
</Student>
</Students>

No comments: