How to Use the XML function in SQL Server 2000 (1)

SQL Server 2000 provides some XML functions, which are used to convert the XML documents in the component layer of the relational row set through XML, read XML documents, and load data in batches. For example, you can pass an XML document to a stored procedure, connect XML to some tables, return a row set, or even modify data in the database. The extended functions of XML in today's enterprise systems promote the introduction of OPENXML functions and for xml statements. Some of these functions not only support XML, but also improve the performance when loading data in batches.

In this article we will discuss how to return XML from SQL Server through the FOR XML clause of the T-SQL. This article will introduce several different methods for returning XML data and architecture information through several examples, and introduce methods for converting XML into a more satisfactory format. Then we will discuss OPENXML and how to connect XML documents to database tables and use the WriteXml and GetXml methods to extract XML from a dataset. The SQL statements of these examples and the sample ASP. NET project that executes some of these examples and exports them as text files can be downloaded from the MSDN Magazine Web site. The sample project also contains the code used to insert and update records into the database from XML.

Return XML

When used in SELECT statements, the for xml clause instructs SQL Server to return data as XML, which is opposite to the standard row set. You can specify the return mode: RAW, AUTO, or EXPLICIT. Each mode provides different XML conversion methods. Figure 1 provides an overview of various modes ).

Figure 1 for xml Schema Overview

Mode	Description
RAW	Each record in a row set is converted into an XML element called a row. The element will contain an attribute to represent the retrieved column.
AUTO	A row set record can be converted to a Nested XML element named after a table in the FROM clause. Each retrieved column is represented as an attribute.
EXPLICIT	Provides many controls for formatting XML. However, the syntax used in the EXPLICIT it mode is much more complex. XSLT is a common XML Conversion method.

FOR example, if you use for xml raw to query the Employees table of the Northwind database Each employee row is returned in the element. Each column in the SELECT statement is expressed An attribute of an element. Select two employee records FOR the following for xml raw query and return them in RAW format:

SELECT EmployeeID, FirstName, LastName FROM Employees WHERE LastName LIKE 'D%' FOR XML RAW

If you modify the SELECT statement, you can use the for xml auto clause. This time, the element is named Employees, which matches the name of the source table. The column is still the attribute of the primary element:

SELECT EmployeeID, FirstName, LastName FROM Employees WHERE LastName LIKE 'D%' FOR XML AUTO

Conversion and hierarchy

Although the difference in the above example is small, the difference between AUTO and RAW is obvious when used together with a query used to join a table. Whether the data is from one table or multiple tables, queries using for xml raw will only return Element. Therefore, the RAW mode does not use the inherent layered structure of XML documents. See the following SQL statement:

SELECT   Customers.CustomerID, CompanyName, OrderID, CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate FROM     Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID

In this example, a one-to-multiple parent-child relationship is retrieved. If this SQL statement is executed, a series of customers and their corresponding orders are returned. If the for xml raw clause is attached and executed again, the obtained XML result contains a ticket indicating each row returned. Element. FOR example, if XML data in figure 2 indicates that CustomerID is ALFKI, for xml raw will return rows.

OrderDate="08/25/1997" /> OrderDate="10/03/1997" /> OrderDate="10/13/1997" /> OrderDate="01/15/1998" /> OrderDate="03/16/1998" /> OrderDate="04/09/1998" />

Figure 2 XML data