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

SQL Server 2000 and XML

A look at some of SQL Server 2000's integrated XML support.

Web services are about linking existing applications and their data to the Web. Many of your existing applications are already using SQL Server for data storage. The easier you can integrate SQL Server with XML and other Web services languages, the better. To that end, here is a short excerpt from the book XML and ASP.NET, featured on InformIT, that discusses the XML support available in SQL Server 2000 along with a short description of a new clause, FOR XML.

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

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.


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.

Read much more about using XML in SQL Server at InformIT.

Dig Deeper on XML in SQL Server

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.