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,

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
<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>
No comments:
Post a Comment