FOR XML Clause

Basic Syntax of the FOR XML Clause

The FOR XML mode can be RAW, AUTO, EXPLICIT, or PATH. It determines the shape of the resulting XML.

use to get output in XML format.

The FOR XML clause appears at the end of your SELECT statement. It provides four modes of XML generation which are RAW, AUTO, PATH and EXPLICIT. Let’s see what these options are.

FOR XML RAW:

It is useful for ad hoc FOR XML querying when the structure of the resultant Xml is not known from beginning. While using this you have to be very careful because the result of this can change dramatically when the underlying data table structure is changed.

FOR XML AUTO:

This is also useful for ad hoc FOR XML querying. This is mainly useful when you need to map the XML result back to the original columns in the source table. Its result can also change dramatically in above case.

FOR XML PATH:

This is designed for explicitly defining XML result structure. This is more appropriate option than above two while you are working in production environment because you always know the resultant XML structure.

FOR XML EXPLICIT:

This is original method for explicitly defining XML result structure. The operation of FOR XML EXPLICIT is more complex and less intuitive than FOR XML PATH. Better to use PATH when you know the structure of your resultant XML.

REMEMBER:

When you select data from SQL Server using FOR XML it will just create an XML fragment and to make it well formed XML you need to add ROOT elements to this fragment. You can add ROOT using FOR XML as well.

When the TYPE option is specified resultant data will be xml data type instance when you want to nest FOR XML option which inserts an inline XML schema to beginning of your XML result.

Understanding PATH Mode

Here I will show you couple of SQL Queries with the use of PATH mode. I am using Adventure works database and SQL server 2008 Enterprise Evolution version these two things can be found at following locations.

http://www.a2zdotnet.com/View.aspx?Id=69

Example:

select * from sim_details(nolock) where MSISDN like '447586653622'

FOR XML PATH,

ELEMENTS XSINIL

result will be in XML format