SQL Server 2000 and XML

Kirk Evans, Ashwin Kamanna and Joel Mueller

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

    Requires Free Membership to View

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 www.microsoft.com/sqlserver.

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.

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.