Developer

SQL Server & XML integration

XML has emerged as the standard for data. Learn how to utilize XML when working with SQL Server databases, focusing on the FOR XML clause.


By Jonathan Lurie & Timothy Stockstill

The newcomer to the ranks of data storage solutions is XML, short for eXtensible Markup Language. XML gives Web developers greater flexibility than HTML. This new technology has sent many development organizations back to the drawing board in desperate attempts to integrate XML into their products before their competition does. One such company, and some say the forerunner, is Microsoft. Microsoft was initially slow to develop an Internet product. I recently read a quote from a ranking Microsoft employee who said, “If you cut us, we bleed XML.” While this may seem a little dramatic, it happens to be true: XML seems to be everywhere in Microsoft products. Let’s see how Microsoft integrated XML in the SQL Server product suite, focusing on the FOR XML clause.

Retrieving information as XML
One point of integration between SQL Server and XML lies in the ability to create an XML file from SQL data. The construction of an XML file is not so complicated that it could not easily be generated using a simple script and an ADO record set. Although this is not a particularly difficult task, it does require developers to produce a different script for each result set they retrieve from the server, or to produce a far more complicated generic script. The SELECT statement now has a new FOR XML clause.

A look at the syntax for this clause shows the following:
[ FOR { XML { RAW | AUTO | EXPLICIT }
[ , XMLDATA ]
[ , ELEMENTS ]
[ , BINARY BASE64 ] } ]

The XML mode of the FOR XML clause is indicated by one of the three values: RAW, AUTO, or EXPLICIT. The mode determines the shape and schema of the resulting XML. Let’s take a closer look at each option with the following examples.

RAW example
We execute the following SQL Statement:
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders.OrderDate, ProductID
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.OrderID
FOR XML RAW

It yields the following result:
<row OrderID="10248" OrderDate="1996-07-04T00:00:00" ProductID="11"/>
<row OrderID="10248" OrderDate="1996-07-04T00:00:00" ProductID="42"/>
<row OrderID="10248" OrderDate="1996-07-04T00:00:00" ProductID="72"/>

AUTO example
We execute the following SQL Statement:
‘Want to limit the result to 3 records.
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders.OrderDate, ProductID
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.OrderID
FOR XML AUTO

It yields the following result:
<Orders OrderID="10248" OrderDate="1996-07-04T00:00:00">
<Order_x0020_Details ProductID="11"/>
<Order_x0020_Details ProductID="42"/>
<Order_x0020_Details ProductID="72"/>
</Orders>

EXPLICIT example
Explicit mode provides the query writer with complete control of the XML being generated. The control comes with a hefty price tag: Each query must be written so that the XML information accompanies each part of the SQL Statement.

The complex syntax for this is beyond the scope of this article. The [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ] indicates that these are optional parameters.

Optional elements
The examples provide a closer look at the inner workings of each setting. Now we’ll take a closer look at the optional elements of the FOR XML statement, XMLDATA

If you specify this option, the XML-Data schema will be included in the result set. Here is the SQL statement:
SET ROWCOUNT 3
SELECT Orders.OrderID, Orders.OrderDate, ProductID
FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.OrderID
FOR XML AUTO, XMLDATA

The previous SQL yields the following result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Orders" content="eltOnly" model="closed" order="many"><element type="Order_x0020_Details" maxOccurs="*"/>…

ELEMENTS
The ELEMENTS option dictates that the columns should be returned as sub-elements, rather than attributes. You can only use this option if you use the AUTO mode.

BINARY BASE64
By using this option you are indicating that you want binary data to be represented in base64-encoded format.

There are many guidelines for using the XML clause—so many that you would do best to refer to the SQL Books online.

Keep your SQL Server skills sharp
SQL Server, Microsoft’s powerful relational database management system, can help with everything from e-commerce to data warehousing. Our tips are delivered each Tuesday and Thursday. Sign up now!

Check online for guidelines
SQL Server Books Online provides many guidelines for working with the FOR XML clause. There are many guidelines with restrictions for using the clause. For example the FOR XML clause cannot be used with a view definition, or with the COMPUTE BY clause. Refer to the documentation for more detail.

While we’ve described the FOR XML clause, you should be aware that this feature is only one of several XML integration points with SQL Server. Other integration points include the OPENXML function and template files in IIS. It might just be true: SQL Server seems to be bleeding XML.

Editor's Picks