Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server support for XML

New clause that allows easy data manipulation.

There are ways that you can use XML to access SQL Server data that make it a snap to use the data for further actions. This tip, excerpted from InformIT, talks about a new feature of SQL Server: "FOR XML clause."

SQL Server 2000 has several key features right out of the box that enable support for XML. Combining these features with ADO.NET and the XML Framework classes provide a broad range of options for working with data, which simplifies data access over the Web.

In addition to the XML support that an out-of-the-box installation of SQL Server provides, Microsoft has also released SQLXML 3.0, a service pack to SQL Server that provides additional features to those already present in SQL Server 2000. The installation executable can be obtained from Microsoft.

SQL Server 2000 XML Support

SQL Server 2000 provides several important options for reading XML data from SQL Server. You can explicitly query the database and request XML data back using the FOR XML clause. You can also query SQL Server over HTTP, which enables you to create XML views of relational data that can be queried by using XPath syntax.

SQL Server 2000 added a set of reserved words that extract data with an XML representation. These reserved words are associated with the FOR XML and OPENXML clauses.


Suppose that you want to represent all the rows in the Customers table in the Northwind database as XML. What is the best approach to solving this problem? One way to do this is to fill an ADO.NET DataSet object, loop through the rows and columns of its Table object, and add each column to an XmlDocument object. You have already seen better ways to handle this problem because the DataSet object provides rich XML support.

Another approach, then, is to simply fill the DataSet object and use the ReadXml method to extract XML from the DataSet object. Both approaches solve the problem by loading all the data into memory and creating an XML representation. SQL Server's XML support provides you with a range of options for working with data as XML.

One way that SQL Server 2000 adds XML support is through the addition of the FOR XML clause. Using this new clause, you can query the database and have the results returned as XML rather than as a tabular resultset. Providing XML bypasses the need to load the tabular data into an interim object, such as ADO.NET, and loop through the records to create an XML representation. Using the FOR XML clause in a SQL statement causes the results to be returned as XML data rather than as tabular data.

FOR XML Returns Multiple Rows When Not Streamed

Behind the scenes, a single column resultset is returned. The results of the XML query are broken up into strings, where each row consists of up to 8,192 characters (the upper limit for the number of characters that a row can contain). But because you receive the entire resultset using a stream, breaking the XML into different rows makes no difference.

The XML returned as a result of a FOR XML query does not contain a root node; it only represents the rows that are returned. When outputting the results from an XML query, add your own root element to make the XML well formed.

By default, elements map to a table or view, and attributes map to the table or view's columns. The FOR XML clause, however, provides several options for returning data that has other structures. The complete syntax of the FOR statement in transact SQL is represented with the following production:

      [ , XMLDATA ]
      [ , ELEMENTS ]
      [ , BINARY BASE64 ]

To read the entire tip from which this tip is excerpted, click over to InformIT. No registration, no muss, no fuss; just good info.

Dig Deeper on XML in SQL Server

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.