XML and SQL Server 2000

PLEASE NOTE: The authors based their SQL Server 2000 articles for this issue on pre-Beta 2 versions, so you might notice some differences between Beta 2 and the behavior or interfaces we describe in this issue. In particular, please note that the indexed views feature will be available only in SQL Server 2000 Enterprise Edition. However, you can install Enterprise Edition on NT 4 Server and Windows 2000 (Win2K) Server. You don't need NT 4.0 Enterprise or Win2K Advanced Server.

One of the most eagerly awaited features in SQL Server 2000, support for XML, is also one of the most nebulous in terms of immediate, practical value. Everyone's heard the hype about a language to bridge all languages, and nearly every relational database management system (RDBMS) now claims XML support. But when and where do you use XML and why?

XML, an Internet standard for information exchange, lets you publish data types in a platform-independent manner, facilitating interoperability and e-commerce. XML also separates data from presentation information within Web pages, giving you a standard way to define and exchange data between applications and databases. (The sidebar "XML—The Current Big Thing," page 42, explores the business benefits of using XML to separate data from its presentation.)

As a language for defining markup languages, XML's primary value will come from either the widespread acceptance of a particular language defined in XML or the widespread acceptance of XML and the availability of utilities, tools, and infrastructure to support its use. Although XML has several excellent defined languages—such as BizTalk, Directory Services Markup Language (DSML), and Simple Object Access Protocol (SOAP)—it isn't for everyone, especially if you're in a pure Microsoft shop developing 32-bit Windows applications. For data transfer across a LAN, ADO result sets are the obvious choice. However, in this Internet age, few organizations are an island unto themselves. And even within organizations, a pure environment of any one server, platform, or language is rare.

Although SQL Server 2000 is the first SQL Server version to feature XML support, Microsoft's XML technology preview runs under versions 7.0 and 6.5. (You can download the technology preview from Microsoft's SQL Server Web site at http://msdn .microsoft.com/workshop/xml/articles/xmlsql/.)You can also provide XML support in SQL Server 7.0, 6.x, and 4.2 by writing extended stored procedures and standard stored procedures, although standard stored procedures can drag down performance for large data sets with a complex structure. In addition, certain SQL Server 7.0 functions, such as full-text search, let you store XML as text. So what features make SQL Server 2000 officially XML-enabled?

Traditionally, you can request two kinds of XML from a database: static XML stored within the database and dynamic XML generated from data within the database. Even the first version of SQL Server could serve as an XML repository, and developers have used pre-SQL Server 2000 versions to create applications that generate XML. But SQL Server 2000 supports an XML data type, letting you store XML natively in the database. Having a native XML data type is just one of the criteria that makes SQL Server 2000 XML-enabled. SQL Server 2000 also lets you use

Let's look at SQL Server 2000's XML enhancements and an example of how you can XML-enable your existing systems by extracting data from SQL Server 2000 as XML, formatting the data the way you want, then using that data in your applications.

Accessing SQL Server Through HTTP

With SQL Server 2000, you can enter an SQL statement into a URL in Microsoft Internet Explorer (IE) 5.0 and retrieve records in XML format. The following pseudo URL query shows the query's various components:

http:///?sql=+FOR+XML+RAW

First, the query uses the HTTP protocol, giving you a large client base for any system you might produce. The query then directs its request to the Web server, Microsoft Internet Information Server (IIS), through a virtual root on the IIS server that you must configure to use the SQL Server XML extensions.

Next, the query requests the data you want. When requesting data from a Web server, you typically use an HTML FORM with one of two standard HTML FORM methods: GET or POST. GET passes FORM data from the client to the Web server as name/value pairs appended to the URL. However, this mechanism has several well-known drawbacks, including a limited URL length and limited security. POST, the preferred method in application development, passes FORM data as name/value pairs stored inside the body of the HTTP request. This article's examples use the GET method to send queries to the database so that you can see the data request.

You can also use SQL statements or stored procedures to access data in XML format. Or you can run an XML template, an XML document that contains one or more valid queries, statements, or update grams. Templates follow the form

sql statement(s)

You can invoke templates by including them in the GET or POST request or by calling them as files saved in the Web server's virtual directory.

If your SQL queries are long, IE can't display them in its location bar and you'll have a hard time typing them again and again during testing. For these and other reasons, you can store template queries in a file in the virtual directory and reference them as follows:

http://auril/Northwind/myquery.xml

The myquery.xml query asks for a given customer ID's customer order information and looks like

 SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM ORDERSWHERE CustomerID=? ORDER BY OrderID XML RAW

You can also use parameters with template queries, then combine the queries with Extensible Style Language (XSL) to provide a quick substitute for boilerplate Active Server Pages (ASP) code that queries a database and formats the resulting records as an HTML table. XSL describes how to format or display XML data. Using template queries with XSL stylesheets this way resembles the use of Hypertext Markup Language extension (HTX) files with Internet Database Connector (IDC), which ASP replaced and improved on. Although SQL Server's XML features alone can't replace ASP, for simple data retrieval, the XML features are faster and easier to implement. Screen 1 shows the output for the following simple query, which combines an XML template query with XSL:

http://auril/Northwind/myquery .xml?CustomerID=vinet&xsl=ptab.xsl

Using FOR XML

Using SQL Server 2000's new FOR XML clause in a SELECT statement lets you retrieve results as an XML document instead of a row set. You can use the FOR XML clause in both queries and stored procedures. Arguments for the FOR XML clause are XML mode, SchemaOption, and ELEMENTS.

XML mode. XML mode specifies the XML mode (RAW, AUTO, or EXPLICIT), which determines the shape of the resulting XML tree (see Table 1 for a description of the modes). EXPLICIT mode is the most useful, letting you create an XML tree that (if you have all the requisite data) lets you provide XML data in your preferred format.

SchemaOption. This option specifies that the database return a schema. SchemaOption can be Document Type Definition (DTD) or XMLData. DTD returns the schema and adds the root element to the results. XMLData returns the schema but doesn't add the root element. If you specify SchemaOption, the clause prepends schema to the document. Otherwise, the database doesn't return any schema.

ELEMENTS. If you specify the ELEMENTS option, the SELECT statement returns columns as subelements. Otherwise, the statement maps the columns to XML attributes. SQL Server 2000 supports this option only in AUTO mode.

You specify XML mode in the SELECT statement's FOR clause by using the syntax

FOR | [XML mode [, SchemaOption] [, ELEMENTS]]

System Stored Procedures

SQL Server 2000 adds six system stored procedures for manipulating XML data: sp_xml_preparedocument, sp_xml_fetchdocument, sp_xml_removedocument, sp_xml_insertfromxml, sp_xml_removexml, and sp_xml_fetchintoxml.

The stored procedures for manipulating XML data are on a per-connection basis, which isn't ideal for n-tier applications, for example, that rely heavily on connection pooling to provide scalability. However, the procedures provide useful functionality, letting you store XML (preparedocument) and retrieve it using a pointer (fetchdocument). You can also delete an entire document or specified elements within the document (removedocument, removexml) and insert into an XML document from a table or into a table from an XML document (insertfromxml, fetchintoxml).

Although SQL Server 2000 provides thorough programmatic access to XML, several XML-specific engines and XML add-ons, such as Oracle8i's interMedia, provide a finer grain of control over XML for such tasks as querying a set of XML documents and finding a specific element or pattern in a document. However, other SQL Server 2000 enhancements, such as user-defined functions, might close the gap by letting you code additional functionality as you need it. But even with its current XML functionality, which lets you use XML as a transport medium for relational data, SQL Server 2000 fully meets the requirements for an XML-enabled database and excels in ease of use. For example, you can build an XML-enabled customer service Web application for the Northwind database in just a few hours.

Update Grams

Update grams are XML-based insert, delete, and update batch operations with the following general format:

To perform an INSERT, you use only an sql:after block. To perform a DELETE, you use only an sql:before block. And to perform an UPDATE, you use both. The database must complete all operations within an sql:sync block for a transaction to succeed.

OLE DB Provider Extensions for XML

SQL Server 2000's new OLE DB provider extensions for XML—ICommandText::SetCommandText and ICommand::Execute—let you set XML documents as command text, execute the command, and retrieve the result as a stream, which you can then use in further processing. For example, you might pass the XML document to the Document Object Model (DOM), an interface that lets programs and scripts dynamically access and update a document's content, structure, and style.

SQL Server also supports templates as command text; you just pass DBGUID_
MSSQLXML as the command's globally unique ID (GUID). The consumer must call ICommand::Execute to execute XML templates. To obtain the result set as an XML document, set the riid to Istream, which tells the provider to return the result set as a stream.

An XML-Enabling Example

The first thing most SQL Server developers will want to do with XML is to get standard relational data into XML format. Although many organizations will be developing new applications as well, the first order of business will be to quickly leverage what they already have by publishing existing data as XML. Here's a simple example that should get you on your way.

First, set up a login and user for the Northwind database to use for all XML-related querying. Then, select Programs, Microsoft SQL Server 2000, Configure SQL XML Support in IIS to reach the screen that lets you configure IIS's XML SQL Server extensions.

Screen 2 shows the configuration screen, with most of the defaults selected. However, I selected Allow URL queries so that I could perform ad hoc queries. As explained earlier, a URL query is an SQL statement embedded in the URL of an IIS machine configured to support SQL Server XML. In ad hoc queries, the URL passes the SQL statement as a single parameter. I also configured the connection settings to use a SQL Server account I had already set up. (Note that SQL Server Books Online—BOL—currently refers to a virtual root of Nwind throughout, so you might want to change the virtual root's value to correspond.)

I'm running both the Web server and SQL Server 2000 on the same, clean installation of Windows 2000 (Win2K). If you install SQL Server 2000 on a machine already running SQL Server 7.0, make sure you refer to the correct SQL Server version.

Now, let's look at a URL query example:

http://auril/  Northwind?sql=   SELECT+CustomerID,  ContactName+  FROM+Customers+  FOR+XML+RAW

where auril is the name of the machine running IIS, /Northwind refers to the virtual directory set up for XML support, and ?sql= is the single parameter that describes an SQL statement. The rest of the query is the actual SQL statement that you encode so that it's safe for passing as a URL. SQL Server receives the statement as

SELECT CustomerID, ContactName FROM Customers FOR XML RAW

The query requests RAW XML, which returns each row as a separate element, with fields in each row listed as separate attributes of that element. Screen 3 shows an example of this query's output.

In n-tier applications that use a limited number of stored procedures with set parameters to control data access, you can modify the database schema without modifying any client code by simply modifying the relevant stored procedures. Consider the stored procedure from the Northwind database in Listing 1. To XML-enable this stored procedure, you create a new stored procedure with the additions highlighted in Listing 2. You then send SQL Server an "execute customerodersxml 'VINET'" request by entering the following URL from IE 5.0:

http://auril/northwind?sql=   execute+custordersordersxml+   VINET

You'll receive the results as XML-formatted records within your browser, as Screen 4 shows. This example uses the "execute " T-SQL convention, but the SQL Server XML extensions for IIS also support the ODBC-style CALL function.

This query demonstrates basic but useful functionality. And by adding XSL, you can quickly generate an HTML document that contains a customer orders tracking report. Just combine this XML document and an XSL document (available online at http:// www.sqlmag.com), which represent a total of about 40 lines of code.

The advantages of the XML method over the traditional ASP method is that XML offers more flexibility for simple data presentation and you can implement it quickly. To show you all the possibilities, you can expect a slew of sample applications by the release version of SQL Server 2000.

Scratching the Surface

With XML, you can define standard document types that let you share, transmit, and transform business information and facilitate automated business processes. The box "XML Links," at left, points to some Web sites that can help you research XML and its benefits.

This article barely scratches the surface of what you can do—and do quickly—with XML in SQL Server 2000. For example, I recently created a simple HTTP-based service—a set of URLs with well-defined query parameters that return XML data—in a staggering 10 minutes. I had the URL running in less time than the phone call asking whether the specification had arrived. This kind of productivity enhancement will make SQL Server 2000 a platform of choice for XML delivery.