Understanding the FOR XML Clause in T-SQL

As discussed, it is very easy to get XML from SQL Server. But we must understand the different methods of retrieving data, and what exactly the output will look like. Let’s start with the FOR XML clause of the SELECT statement, which looks like this:

 Select fields from table FOR XML mode [,XMLDATA] [,ELEMENTS] [,BINARY BASE64]

The AUTO mode of the FOR XML statement tells SQL Server that you want field names as attributes, not as elements.

Mode can be any one of the following: RAW, AUTO, or EXPLICIT. RAW transforms each row from the query result into an XML element with a generic identifier row as the element tag. So the following XML document:

 <?xml version="1.0" encoding="utf-8" ?> - <MyCustomers>       <row CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" />       <row CompanyName="Ana Trujillo Emparedados y helados"       ContactName="Ana Trujillo" />   </MyCustomers >

is returned from the following statement:

 http://localhost/xdesk?sql=select top 2 CompanyName, ContactName from customers for XML Raw&root=MyCustomers

AUTO returns the XML results as a nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. Each of the columns listed in the SELECT clause is mapped to the element’s attributes.

   <?xml version="1.0" encoding="utf-8" ?> - <MyCustomers>   <customers CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" />   <customers CompanyName="Ana Trujillo Emparedados y helados"       ContactName="Ana Trujillo" />   </MyCustomers> 

The preceding XML document is returned from the following query:

 http://localhost/xdesk?sql=select top 2 CompanyName, ContactName from customers for XML Auto&root=MyCustomers

EXPLICIT You can EXPLICITly define the shape of the XML tree. This requires queries to be written in a certain way, so that the additional information you wish to supply is made part of the XML document.

Optional parameters are XMLDATA, ELEMENTS, and BINARY BASE64.

XMLDATA Returns the XML schema with the result set. The root element is not added to the result set; the schema is appended to the document.

ELEMENTS Columns are returned as subelements. The default is mapping columns to XML attributes.

   <?xml version="1.0" encoding="utf-8" ?> - <MyCustomers> - <customers>   <CompanyName>Alfreds Futterkiste</CompanyName>   <ContactName>Maria Anders</ContactName>   </customers> - <customers>   <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>   <ContactName>Ana Trujillo</ContactName>   </customers>   </MyCustomers >

The preceding document is returned from this query (notice the [,ELEMENTS] parameter specified after the FOR XML AUTO):

 http://localhost/xdesk?sql=select top 2 CompanyName, ContactName from customers for XML Auto, ELEMENTS&root=MyCustomers

BINARY BASE64 Binary data from the query is returned in base64-encoded format. This must be specified when retrieving binary data using RAW and EXPLICIT mode. This is the default in AUTO mode.

FOR XML Type Mode

When FOR XML was introduced in SQL Server 2000, its result was directly returned to the client in text form. Now that SQL Server 2005 supports the xml data type, you can optionally request that the result of a FOR XML query be returned as a variable in the xml data type. This is achieved by simply specifying the TYPE directive in the FOR XML query. This means that you can process the result of a FOR XML query on the server and then specify an XQuery against it, or assign the result to an xml type variable, or write Nested FOR XML queries in your backend code.

The following code demonstrates the use of FOR XML query with TYPE directive.

 SELECT FirstName, LastName, Emaill FROM CRM. Customer ORDER BY LastName FOR XML AUTO, TYPE

And returns something like

 <Customer FirstName="Jeff" LastName="Shapiro" Emaill ="[e-mail]"/>

With the FOR XML clause specifying the TYPE directive, the XML is returned as xml type and is assigned to a variable. You can also use the FOR XML query results in INSERT, UPDATE, and DELETE statements The following code illustrates the FOR XML returning returns an instance of xml type, and in turn the INSERT statement inserts this XML into a table:

 CREATE TABLE Tablel (Col1 int, XmlCol xml) go INSERT INTO Table 1 VALUES (1, '<Root><Item / >< /Root>' ) go  CREATE TABLE Table2 (XmlCol xml) go INSERT INTO Table2 (XmlCol) SELECT (SELECT XmlCol.query ('/Root') FROM Table 1 FOR XML AUTO, TYPE) go

PATH Mode

The new PATH mode makes it easier to combine elements and attributes as a simpler way to introduce additional nesting when you need to represent complex properties. In other words, the PATH mode provides a simpler alternative to the more complex EXPLICIT mode queries.

By specifying PATH mode, column names or column aliases are treated as XPath expressions. These expressions in turn are used to map values to XML. Here is a list of conditions for mapping columns in a rowset:

• Columns without a name

• Columns with a name

• Columns with a name specified as a wildcard character (*)

• Columns with the name of an XPath Node Test

• Column names with the path specified as data()

• Columns that contain a NULL value by default

• Columns without a name

Here is an example:

 SELECT 2+1 FOR XML PATH

Generates the following output:

 <row>3</row>