How to use the XML features in SQL Server 2000

In this article, we will discuss how to return XML from SQL Server through a-SQL FOR XML clause. This article will introduce several different ways to return XML data and schema information through a few examples, and also introduce ways to transform XML into more satisfying formats. It then discusses OPENXML, as well as ways to join XML documents to database tables and extract XML from datasets using the WriteXml and Getxml methods.

SQL Server 2000 provides some XML functionality for transforming relational rowset XML documents, reading XML documents, and bulk loading data through XML. For example, you can pass an XML document to a stored procedure, link the XML to some tables, return a rowset, or even modify the data in the database. The expanding functionality of XML in today's enterprise systems facilitates the introduction of OPENXML functions and for XML statements. Some of these features not only support XML, but also improve performance when loading data in bulk.

In this article, we will discuss how to return XML from SQL Server through a-SQL FOR XML clause. This article will introduce several different ways to return XML data and schema information through a few examples, and also introduce ways to transform XML into more satisfying formats. It then discusses OPENXML, as well as ways to join XML documents to database tables and extract XML from datasets using the WriteXml and Getxml methods. These examples of SQL, as well as sample asp.net projects that perform some of these examples and export them as text files, can be downloaded from the MSDN Magazine Web site. The sample project also contains code for inserting and updating records from XML to the database.

return XML

When used in a SELECT statement, the FOR XML clause instructs SQL Server to return the data as XML, as opposed to a standard rowset. You can specify the return mode: RAW, auto, or explicit. Each pattern provides different ways of converting XML (Figure 1 gives an overview of the various patterns).

Figure 1 FOR XML Schema overview

Mode	Description
RAW	Each record in a rowset is converted to an XML element called a row. The <row> element will contain an attribute that represents the column being retrieved.
AUTO	The rowset record can be converted to a nested XML element named after the table in the FROM clause. Each column retrieved will be represented as a property
EXPLICIT	Provides many controls for formatting XML. However, the syntax of the explicit mode is much more complicated. XSLT is a more common method of XML conversion.

For example, if you use for XML raw to query the Employees table of the Northwind database, it returns each employee row in the <row> element. Each column contained in a SELECT statement is represented as an attribute of the <row> element. The following for XML Raw query selects two employee records, and then returns in RAW format:

SELECT EmployeeID,
FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'
FOR XML RAW
<row EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<row EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>

You can use the FOR XML Auto clause to modify the SELECT statement. This time, the element is named employees, which matches the name of the source table. Columns are still properties of the main element:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName LIKE 'D%'
FOR XML AUTO
<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
<Employees EmployeeID="9" FirstName="Anne" LastName="Dodsworth"/>