Retrieving Data in XML Format

SQL Server 2000 provides several ways to retrieve data in XML format from SQL Server. You can retrieve XML data through normal database connections or across a HyperText Transfer Protocol (HTTP) connection, such as an intranet or even the Internet. When connecting over HTTP, you can even use XML to communicate with the database server, in addition to getting results formatted in XML. First, I want to show you how to retrieve data formatted in XML, regardless of how you're connecting to your database or the format of your queries.

FOR XML

SQL Server 2000 adds to the SELECT statement a new clause, called the FOR XML clause, which instructs SQL Server to return the result of a query in XML format. You even have control over the format of the XML returned. The easiest way to understand how this feature works is to look at an example.

Suppose you were working with the Northwind database that ships with SQL Server, and you wanted to see the names of the customers who live in Germany. Customer information in the Northwind database is stored in a table called Customers. Normally you would issue the following query to return this result set:

SELECT ContactName
FROM Customers
WHERE Country = 'Germany'

To return the results in XML format, you simply change this code by adding the FOR XML clause, along with at least one argument that I'll explain shortly.

SELECT ContactName
FROM Customers
WHERE Country = 'Germany'
FOR XML RAW

This query returns the following results (only the first few rows are shown for brevity):

<row ContactName="Maria Anders" />
<row ContactName="Hanna Moos" />
<row ContactName="Sven Ottlieb" />
...

By simply adding “FOR XML RAW,” SQL Server returned the results in XML format. If you have access to SQL Server 2000, you can issue this same query on the Northwind database, and you'll see the same results, providing you haven't modified the database.

The last part of the FOR XML clause shown indicates the mode, or format, of the returned XML content. For this example, I chose the RAW mode, which simply returns each row in an element named row, and columns of the row are returned as attributes of the element.

FOR XML AUTO

Two other modes, AUTO and EXPLICIT, are available. The mode AUTO tells SQL Server to produce some additional information about our results in the returned XML content. Consider the differences between the use of the RAW mode in the preceding example and the use of the AUTO mode in the following example:

SELECT ContactName
FROM Customers
WHERE Country = 'Germany'
FOR XML AUTO

This query produces the following XML output:

<customers ContactName="Maria Anders" />
<customers ContactName="Hanna Moos" />
<customers ContactName="Sven Ottlieb" />
...

In this case, SQL Server names each row element with the name of the table in the result set. Again, columns from the table are returned as attributes. What happens if we return columns from more than one table? Suppose we want to see a list of customers and all of the orders each customer has placed.

SELECT ContactName, OrderID
FROM Customers
   INNER JOIN Orders ON Customers.CustomerID = Orders.OrderID
WHERE Customers.Country = 'Germany'
FOR XML AUTO

This query returns the following XML results:

<customers ContactName="Maria Anders">
  <orders OrderID="10643" />
  <orders OrderID="10692" />
   ...
  </customers>
<customers ContactName="Hanna Moos">
  <orders OrderID="10501" />
  <orders OrderID="10509" />
   ...
  </customers>
<customers ContactName="Sven Ottlieb">
  <orders OrderID="10363" />
   ...

As you can see, the inner table row elements were returned as children of the outer table's elements. So all of the orders for each customer are listed as child rows of the corresponding customer.

FOR XML EXPLICIT

I'm not going to discuss the third mode, EXPLICIT, thoroughly because it is a lengthy topic, the details are beyond the scope of this book, and I don't necessarily recommend using it unless you find a really good reason to use it. This mode gives you more explicit control over the format of the XML output returned in response to queries. For example, if you wish to format the results of a query to conform to a complicated XML schema that represents a purchase order document that you use to communicate with e-commerce trading partners, an XML document produced with the RAW or AUTO formats probably won't suffice. You could still use those formats and transform the results into your purchase order document format, programmatically or with XSLT transformations. Or you could choose to use the EXPLICIT mode and format them as they're returned from SQL Server. It can be quite cumbersome to format queries using the EXPLICIT mode. Later, for retrieving XML results from SQL Server, I'll show you a different method that can be a useful alternative to the EXPLICIT mode of the FOR XML clause.