Monday, December 24, 2012

Return SQL Query Result as XML in SQL Server

SQL Server gives you option to retrieve data as XML by supporting For XML clause, which you can use as a part of your query. For XML clause supports different options that let you define the format of XML data.

For XML clause supports four modes- RAW, AUTO, EXPLICIT, PATH. When you include the FOR XML clause in your query, you must specify one of these four modes.
In this article, I explain how to use each of these modes to retrieve data as XML. I will use following table for query.

The RAW Mode

The RAW  mode generates a single XML element for each row in the result set returned by query. To use For XML clause in RAW mode, you simply append the clause and RAW  keyword to your select statement, as shown in the following example.
select emp.SNO ,emp.Name
from EmpName emp
For XML Raw

Above statement will return the data as following XML.
<row SNO="1" Name="Ashish" />
<row SNO="2" Name="Prashant" />

As you can see, each <row> element maps to row that is returned by select statement and each column name, by default, is treated as an attribute of <row> element.
In the above example, you see that each element of XML is named <row> by default, you can changed this default behavior by providing the name for the element, as shown in the following query.
select emp.SNO ,emp.Name
from EmpName emp
For XML Raw('Employee')

Now this statement will retun the data as following XML.
<Employee SNO="1" Name="Ashish" />
<Employee SNO="2" Name="Prashant" />

In addition to name the row element, you can also specify to create the root element of the XML. For creating the root element add ROOT  keyword to your For XML clause separated by comma.
select emp.SNO ,emp.Name
from EmpName emp
For XML Raw('Employee'), Root('Employees')

Now the resultant XML will be.
<Employees>
  <Employee SNO="1" Name="Ashish" />
  <Employee SNO="2" Name="Prashant" />
</Employees>

Up to this point, the examples I’ve shown you have added column values as attributes to each row element. This is the default behavior of the RAW mode. However, you can instead specify that the column values be added as child elements to the row element by including the ELEMENTS option in the FOR XML clause.
select emp.SNO ,emp.Name
from EmpName emp
For XML Raw('Employee'), Root('Employees'), Elements

Now the resultant XML will be.
<Employees>
  <Employee>
    <SNO>1</SNO>
    <Name>Ashish</Name>
  </Employee>
  <Employee>
    <SNO>2</SNO>
    <Name>Prashant</Name>
  </Employee>
</Employees>

To be continued...
Happy reading!!

No comments:

Post a Comment

^ Scroll to Top