SQL SERVER FOR XML

With the release of SQL Server 2000, Microsoft introduced the capability of formatting return data streams from SQL Server as XML, as opposed to the standard tabular data stream format that is typical for SQL Server. This capability has been improved in every subsequent release, giving the database developer the ability to easily reformat relational data into XML as it is extracted and returned to the client.

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.

The basic structure of the FOR
XML clause looks like this:
[ FOR ]
::=
XML
{
{ RAW [ (‘ElementName’) ] | AUTO }
[

[ , { XMLSCHEMA [ (‘TargetNameSpaceURI’) ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ]
]
| EXPLICIT
[

[ , XMLDATA ]
]
| PATH [ (‘ElementName’) ]
[

[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
::=
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ (‘RootName’) ] ]

This syntax illustrates that you can follow the FOR XML statement with one of four options:
RAW, AUTO, EXPLICIT, or PATH.

Creating XML with RAW Mode Queries:


Consider i have a tables for Associates as show below,




RAW mode is the simplest XML output structure. This example shows the use of the RAW mode query in its simplest form.

select FirstName,LastName from Associate 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. Although the generic row element does not provide any useful information, each attribute name comes directly from the underlying catalog. Here are a few rows of results from this query:

<row FirstName="Vijaynath" LastName="Viswanathan"/>
<row FirstName="Sreelas" LastName="Sreekumar"/>
<row FirstName="Serosh" LastName="K Vikraman"/>

One thing about this output that stands out is that as written, it is technically not a well-
formed XML document. This example requires the addition of a root element to make it well
formed. Microsoft refers to this structure as a well-formed fragment, meaning that if you were to wrap this entire block into a root element, it would be well-formed. To add this root element, use the ROOT condition in the query.

We can also return the list as element-based XML as opposed to the current attribute-based format. Simply add the ELEMENTS condition to the clause, and the query will return elements instead of attributes. However, because some of the person rows listed do not have last names, this can result in missing elements. Use the XSINIL condition with the ELEMENTS condition to solve this problem. Combining these techniques would modify the
query to look like this:

SELECT
FIRSTNAME,
LASTNAME,
EMAIL
FROM ASSOCIATE
FOR XML RAW('ASSOCIATE'),
ROOT('ASSOCIATES'),
ELEMENTS XSINIL

output would be,

<ASSOCIATES xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ASSOCIATE>

<FIRSTNAME>VijaynathFIRSTNAME>

<LASTNAME>ViswanathanLASTNAME>

<EMAIL>vijaynath@live.inEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>SreelasFIRSTNAME>

<LASTNAME>SreekumarLASTNAME>

<EMAIL>hello@abc.comEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>SeroshFIRSTNAME>

<LASTNAME>K VikramanLASTNAME>

<EMAIL>abc@abc.comEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>AMITHFIRSTNAME>

<LASTNAME>KLASTNAME>

<EMAIL xsi:nil="true" />

ASSOCIATE>

ASSOCIATES>

Creating XML with AUTO Mode Queries:

Using AUTO mode in the FOR XML clause will give you fewer options than using RAW mode, because SQL Server generates the XML structure for you. Although you can still use the ROOT, ELEMENTS, and XSINIL directives, you cannot rename the row tags. The following query uses the AUTO mode.

SELECT
FIRSTNAME,
LASTNAME,
EMAIL
FROM ASSOCIATE
FOR XML AUTO,
ROOT('ASSOCIATES'),
ELEMENTS XSINIL

Again we get the same output,

<ASSOCIATES xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ASSOCIATE>

<FIRSTNAME>VijaynathFIRSTNAME>

<LASTNAME>ViswanathanLASTNAME>

<EMAIL>vijaynath@live.inEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>SreelasFIRSTNAME>

<LASTNAME>SreekumarLASTNAME>

<EMAIL>hello@abc.comEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>SeroshFIRSTNAME>

<LASTNAME>K VikramanLASTNAME>

<EMAIL>abc@abc.comEMAIL>

ASSOCIATE>

<ASSOCIATE>

<FIRSTNAME>AMITHFIRSTNAME>

<LASTNAME>KLASTNAME>

<EMAIL xsi:nil="true" />

ASSOCIATE>

ASSOCIATES>