XML is quickly becoming the preferred method of passing information, not only for the Internet, but also across applications and within applications.
Until now, developers have been forced to create our own routines to convert data stored in a database system into XML. With the release of Microsoft SQL Server 2000, however, you can query data directly from SQL Server in XML format.
With the advent of XML and the increasing requirements for distributed applications in today's marketplace, a larger demand has been placed on the developer to provide messaging and data in XML format. Until now, this has consisted of querying data to generate a cursor or ADO RecordSet, then using a conversion routine to change the data from its native representation into XML format. Now, with new features in SQL Server 2000, this task can be accomplished with minimal effort, allowing the developer to concentrate on the more important task of writing business logic.
SQL Server 2000 provides a new feature that allows the developer to query SQL Server and receive data in XML format through the use of a special clause: FOR XML. This clause provides 3 different options for the XML format returned: AUTO, RAW, and EXPLICIT.
Issuing the SQL Select command with FOR XML AUTO will return the result set in XML format, with each record having a node whose tag name is the same as the table name on which the query was performed. Each node will have attributes equal to the fieldnames specified in the query with values equal to the values of the fields within the table. The FOR XML RAW clause will return XML in which each record is represented by a node whose tag name is “row” and whose attributes are the fields from the query. The last option, FOR XML EXPLICIT, uses queries written in a specific format to return the XML in a specific format. Typically, these options are available from within the SQL Server Query Analyzer or an HTTP based URL. However, the FOR XML clause can be used in any query. In that case, XML data will be marshaled into the result set, which could be an ADO RecordSet or a Visual FoxPro cursor (among others). To use the XML in those scenarios, additional extraction is required. This article focuses only on URL-based use.
Configuring SQL Server 2000 for XML Support
Before you can use the new XML support within SQL Server 2000, you must first configure it for that option. Simply click on Start/Programs/Microsoft SQL Server/Configure SQL XML Support in IIS. This will launch the configuration utility necessary to turn on XML support.
Once you have the IIS Virtual Directory Management for SQL Server open, drill down in the tree to the left until you get to the Default Web Site node. On the right side of the screen, right-click and select New/Virtual Directory. This will bring up the New Virtual Directory Properties dialog where you enter information about how SQL Server 2000 should be configured to run when accessed from a URL.
You will be required to give the new virtual directory a name, the physical path on the hard drive associated with this virtual directory, how you wish to log into SQL Server 2000, the SQL Server installation to use, the database name and the different types of queries that can be run: URL queries, Template queries, and/or Xpath queries. More details about URL and Template queries will be discussed below. Xpath queries will be discussed in the next issue of CODE Magazine.
Once you have that information set up, you may also specify virtual names (or virtual subdirectories) beneath the virtual directory in which you may execute Template queries using schemas, Xpath queries, etc. Setting up these additional virtual subdirectories allows the developer to navigate to a file rather than having to specify the entire query as part of the URL.
URL Queries
The easiest way to test or to become familiar with URL queries in SQL Server is to open Internet Explorer and enter queries into the Address textbox. It is important to keep in mind, however, that the XML string returned by SQL Server 2000 is not “well-formed.” The returned XML is a fragment with no root element. However, a parameter can be passed along with the query to specify the root node, which will eliminate this problem by wrapping the returned XML string with the node specified.
For instance, the following query:
SELECT * FROM Customers
as a URL query looks like this:
http://localhost/sql2000?sql=select+*+from+customers+for+xml+auto&root=results
This returns all of the records in the customers table wrapped with a root node called “results”. (See Figure 1)
As shown, each record is represented by a node called “customers” whose attributes are the fields within the customers table. Navigating to the following URL yields a slightly different result:
http://localhost/sql2000?sql=select+*+from+customers+for+xml+raw&root=results
This returns all records in the customers table with a tag named “results” as the root node. The main difference here is that every record is represented by a tag named “row” rather than “customers” (See Figure 2). This is a result of the keywords “for xml raw.”
Both versions of the returned XML appear very similar to the way ADO stores and loads RecordSets in XML format. However, this may not always coincide wih the form of XML that is expected within an application. For this reason, another parameter, xsl, can be included with the URL to specify an XSL style sheet to be used to transform the native XML format from SQL Server 2000 into the expected format. Let's look at the following example of an XSL style sheet called Customers1.xsl which, in this case, resides in the root of the Virtual Directory SQL2000:
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="uri:xsl">
<xsl:template match="/">
<CUSTOMERS>
<xsl:for-each select="results/customers">
<CUSTOMER>
<CUSTOMERID>
<xsl:value-of select="@CustomerID"/>
</CUSTOMERID>
<COMPANY>
<xsl:value-of select="@CompanyName"/>
</COMPANY>
<CONTACT>
<xsl:value-of select="@ContactName"/>
</CONTACT>
<ADDRESS>
<xsl:value-of select="@Address"/>
</ADDRESS>
<CITY>
<xsl:value-of select="@City"/>
</CITY>
<PHONE>
<xsl:value-of select="@Phone"/>
</PHONE>
</CUSTOMER>
</xsl:for-each>
</CUSTOMERS>
</xsl:template>
</xsl:stylesheet>
When the following URL query is executed, the above style sheet is applied to the results and a different version of XML is returned (See Figure 3):
http://localhost/sql2000?sql=select+*+from+customers+for+xml+auto&root=results&xsl=customers1.xsl
Since an XSL style sheet can be applied to the result set, we can also return the output as HTML. Here's a Stylesheet that produces HTML output:
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="uri:xsl">
<xsl:template match="/">
<TABLE width="100%">
<TR bgcolor="moccasin">
<TD valign="top"><b>Customer Id</b></TD>
<TD valign="top"><b>Company</b></TD>
<TD valign="top"><b>Contact</b></TD>
<TD valign="top"><b>Address</b></TD>
<TD valign="top"><b>City</b></TD>
<TD valign="top"><b>Phone</b></TD>
</TR>
<xsl:for-each select="results/customers">
<TR bgcolor="white">
<TD valign="top">
<xsl:value-of select="@CustomerID"/>
</TD>
<TD valign="top">
<xsl:value-of select="@CompanyName"/>
</TD>
<TD valign="top">
<xsl:value-of select="@ContactName"/>
</TD>
<TD valign="top">
<xsl:value-of select="@Address"/>
</TD>
<TD valign="top">
<xsl:value-of select="@City"/>
</TD>
<TD valign="top">
<xsl:value-of select="@Phone"/>
</TD>
</TR>
</xsl:for-each>
</TABLE>
</xsl:template>
</xsl:stylesheet>
Coupled with the contenttype parameter, this Stylesheet allows the browser to display the result set as HTML. By specifying that the contenttype should be text/html, the browser will interpret the result set as HTML rather than XML, which is the default for SQL Server 2000 (See Figure 4).
Template Queries
Another method of retrieving an XML result set from SQL Server 2000 is to use what's called “Template Queries.” These are XML files that tell SQL Server how to run queries, what the root node will be, etc. These files eliminate the need to specify the select statement in the URL.
Let's have a look at our original query:
SELECT * FROM Customers
As a Template query, this would appear as follows:
<results xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
select * from customers for xml auto
</sql:query>
</results>
To run this query, save the above template as Customers1.xml and store it in the Template virtual directory and simply navigate to http://localhost/sql2000/template/customers1.xml, which returns the XML in the native SQL Server format (Figure 1). Replacing the keyword AUTO with RAW in the above Template query will return the results shown in Figure 2.
To apply a style sheet to the result set, the Template query would appear as:
<results xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl='../customers1.xsl'>
<sql:query>
select * from customers for xml auto
</sql:query>
</results>
This will return the results as shown in Figure 3. As with URL queries, you can format the final results to be displayed in HTML. In this case, simply change the referenced Stylesheet from “customers1.xsl” to “customers2.xsl”. The results will be the HTML table created earlier (Figure 4).
Template queries also have the potential to accept parameters to help filter the result set. In this case, the Template file will look like:
<results xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl='../customers1.xsl'>
<sql:header>
<sql:param name='CustomerId'>%</sql:param>
</sql:header>
<sql:query>
select * from customers where customerid like @CustomerId for xml auto
</sql:query>
</results>
In this example, we specify that this Template query will accept one parameter named “CustomerId”, and that this parameter has a default value of %, which in conjunction with the query, will return all records from the customers table. To execute this query, simply navigate to: http://localhost/sql2000/template/customers4.xml?CustomerId=A%25. This will return only those records from the customers table where the CustomerId field begins with an A (Figure 5). Note that we have to use “%25” in the URL, since %`` is not a valid character by itself, but indicates that we reference a character by its ASCII value (25 is the ASCII value of %``, which makes this a little confusing).
Conclusion
XML is becoming an increasingly important part of today's development techniques. With the increasing need for distributed applications, XML is the preferred method for passing information, including data. Now, with SQL Server 2000's support for XML, developers no longer need to use conversion routines to present data in an XML format, leaving them with more time to concentrate on the business logic aspects of application development.
