email article printer friendly more resources

XML-Enable Your SQL Data
Learn how SQL Server's XML features can provide your databases with new functionality.
by Dianne Siebold

April 2003 Issue

For this solution: SQL Server, XML

Given you work in the IT industry, you've most likely heard about XML; but if your work focuses on SQL Server, you might not have worked with XML directly. XML is already the ubiquitous data format in the Web programming environment, and it's one of the primary underlying technologies in the .NET Framework. SQL Server provides support for XML in two ways: through features native to SQL Server and through releases of additional functionality called SQLXML. SQLXML extends SQL Server and provides XML compatibility. In this month's column, I'll look at SQL Server's built-in support for XML as well as some of the features added through subsequent SQLXML releases (see Figure 1). SQL Server's support for XML means that updating and retrieving data is more efficient now; it's no longer necessary to translate XML data into another format that can be understood by a database or to translate XML data from a database into XML. Developers also have a wider range of methods to choose from, meaning they have more data access flexibility.

There's a lot of hype around XML, so it's important to realize that it is a simple technology. It's essentially a standard file format for describing data. (For more information on XML basics, see the sidebar "XML 101.") Support for XML has been a part of SQL Server since the initial release of SQL Server 2000. SQL Server doesn't support XML by providing a method for storing XML documents; instead it provides an interface to relational data so you can read and write XML data in tables and other database objects. The native XML features in SQL Server include the capability to access SQL Server through HTTP, template queries, the FOR XML clause, and the OPENXML() function. Next, I'll discuss how these features work and explain how they can benefit your organization.

Figure 1 Serving Up XML

To access a SQL Server database through HTTP, you must first set up a virtual directory. The virtual directory provides a link between the HTTP protocol and a specific database. To set up a virtual directory, use the "Configure SQL XML Support In IIS" menu item, accessible from Window's Start menu's SQL Server menu item. This is where you specify the name of the virtual directory, the physical path, server name, database name, and login information. Once you create a virtual directory, you can send a query to the database through a URL. If you set up a virtual directory called Northwind and enter the query http://localhost/Northwind?sql=SELECT+*+FROM+Shippers+FOR+XML+AUTO,ELEMENTS+&root=Shippers in your browser, it returns XML similar to the Shippers XML example in the "XML 101" sidebar. HTTP queries make data access in your Web sites and Web applications much simpler than using ADO or any other technologies.