Using SQL Server's XML Support

This chapter is from the book

Guru's Guide to SQL Server Architecture and Internals, The

Learn More Buy

This chapter is from the book

This chapter is from the book 

Guru's Guide to SQL Server Architecture and Internals, The

Learn More Buy

The easiest way to test the virtual directory you built earlier is to submit a URL query that uses it from an XML-enabled browser such as Internet Explorer. URL queries take this form:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO &root=Customers

Here, localhost is the name of the Web server. It could just as easily be a fully qualified DNS domain name such as http://www.khen.com. Northwind is the virtual directory name we created earlier.

A question mark separates the URL from its parameters. Multiple parameters are separated by ampersands. The first parameter we pass here is named sql. It specifies the query to run. The second parameter specifies the name of the root element for the XML document that will be returned. By definition, you get just one of these per document. Failure to specify a root element results in an error if your query returns more than one top-level element.

To see how this works, submit the URL shown in Listing 18.25 from your Web browser. (Be sure to change localhost to the correct name of your Web server if it resides on a different machine).

Listing 18.25

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE
+CustomerId='ALFKI'+FOR+XML+AUTO

(Results)

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
ContactName="Maria Anders" ContactTitle="Sales Representative"
Address="Obere Str. 57" City="Berlin" PostalCode="12209"
Country="Germany" Phone="030-0074321" Fax="030-0076545" />

Notice that we left off the root element specification. Look at what happens when we bring back more than one row (Listing 18.26).

Listing 18.26

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+
WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO

(Results abridged)

The XML page cannot be displayed
Only one top level element is allowed in an XML document.
Line 1, Position 243

Since we're returning multiple top-level elements (two, to be exact), our XML document has two root elements named Customers, which, of course, isn't allowed since it isn't well-formed XML. To remedy the situation, we need to specify a root element. This element can be named anything—it serves only to wrap the rows returned by FOR XML so that we have a well-formed document. Listing 18.27 shows an example.

Listing 18.27

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE
+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO
&root=CustomerList

(Results)

<?xml version="1.0" encoding="utf-8" ?>
<CustomerList>
  <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
    ContactName="Maria Anders" ContactTitle="Sales Representative"
    Address="Obere Str. 57" City="Berlin" PostalCode="12209"
    Country="Germany" Phone="030-0074321" Fax="030-0076545" />
  <Customers CustomerID="ANATR" CompanyName=
    "Ana Trujillo Emparedados y helados" ContactName="Ana Trujillo"
    ContactTitle="Owner" Address="Avda. de la Constituci—n 2222"
    City="México D.F." PostalCode="05021" Country="Mexico"
    Phone="(5) 555-4729" Fax="(5) 555-3745" />
</CustomerList>

You can also supply the root element yourself as part of the sql parameter, as shown in Listing 18.28.

Listing 18.28

http://localhost/Northwind?sql=SELECT+'<CustomerList>';
SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR
+CustomerId='ANATR'+FOR+XML+AUTO;
SELECT+'</CustomerList>';

(Results formatted)

<CustomerList>
  <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
    ContactName="Maria Anders" ContactTitle="Sales Representative"
    Address="Obere Str. 57" City="Berlin" PostalCode="12209"
    Country="Germany" Phone="030-0074321" Fax="030-0076545" />
  <Customers CustomerID="ANATR" CompanyName=
    "Ana Trujillo Emparedados y helados" ContactName="Ana Trujillo"
    ContactTitle="Owner" Address="Avda. de la Constituci—n 2222"
    City="México D.F." PostalCode="05021" Country="Mexico"
    Phone="(5) 555-4729" Fax="(5) 555-3745" />
</CustomerList>

The sql parameter of this URL actually contains three queries. The first one generates an opening tag for the root element. The second is the query itself, and the third generates a closing tag for the root element. We separate the individual queries with semicolons.

As you can see, FOR XML returns XML document fragments, so you'll need to provide a root element in order to produce a well-formed document.

Special Characters

Certain characters that are perfectly valid in Transact-SQL can cause problems in URL queries because they have special meanings within a URL. You've already noticed that we're using the plus symbol (+) to signify a space character. Obviously, this precludes the direct use of + in the query itself. Instead, you must encode characters that have special meaning within a URL query so that SQLISAPI can properly translate them before passing on the query to SQL Server. Encoding a special character amounts to specifying a percent sign (%) followed by the character's ASCII value in hexadecimal. Table 18.3 lists the special characters recognized by SQLISAPI and their corresponding values.

Here's a URL query that illustrates how to encode special characters.

http://localhost/Northwind?sql=SELECT+'<CustomerList>';SELECT+*+FROM+Customers+ WHERE+CustomerId+LIKE+'A%25'+FOR+XML+AUTO;SELECT+'</CustomerList>';

This query specifies a LIKE predicate that includes an encoded percent sign (%), Transact-SQL's wildcard symbol. Hexadecimal 25 (decimal 37) is the ASCII value of the percent sign, so we encode it as %25.

Table 18.3. Special Characters and Their Hexadecimal Values

Style Sheets

In addition to the sql and root parameters, a URL query can also include the xsl parameter in order to specify an XML style sheet to use to translate the XML document that's returned by the query into a different format. The most common use of this feature is to translate the document into HTML. This allows you to view the document using browsers that aren't XML aware and gives you more control over the display of the document in those that are. Here's a URL query that includes the xsl parameter:

http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl

Listing 18.29 shows the XSL style sheet it references and the output produced.

Listing 18.29

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      version="1.0">
  <xsl:template match="/">
    <HTML>
      <BODY>
        <TABLE border="1">
          <TR>
            <TD><B>Customer ID</B></TD>
            <TD><B>Company Name</B></TD>
          </TR>
          <xsl:for-each select="CustomerList/Customers">
            <TR>
              <TD>
              <xsl:value-of select="@CustomerId"/>
              </TD>
              <TD>
              <xsl:value-of select="@CompanyName"/>
              </TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
  </xsl:template>
</xsl:stylesheet>

(Results abridged)

Content Type

By default, SQLISAPI returns the results of a URL query with the appropriate type specified in the header so that a browser can properly render it. When FOR XML is used in the query, this is text/xml unless the xsl attribute specifies a style sheet that translates the XML document into HTML. In that case, text/html is returned.

You can force the content type using the contenttype URL query parameter, like this:

http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl&contenttype=text/xml

Here, we've specified the style sheet from the previous example in order to cause the content type to default to text/html. Then we override this default by specifying a contenttype parameter of text/xml. The result is an XML document containing the translated result set, as shown in Listing 18.30.

Listing 18.30

<HTML>
  <BODY>
    <TABLE border="1">
      <TR>
        <TD>
          <B>Customer ID</B>
        </TD>
        <TD>
          <B>Company Name</B>
        </TD>
      </TR>
      <TR>
        <TD>ALFKI</TD>
        <TD>Alfreds Futterkiste</TD>
      </TR>
      <TR>
        <TD>ANATR</TD>
        <TD>Ana Trujillo Emparedados y helados</TD>
      </TR>
      <TR>
        <TD>WILMK</TD>
        <TD>Wilman Kala</TD>
      </TR>
      <TR>
        <TD>WOLZA</TD>
        <TD>Wolski Zajazd</TD>
      </TR>
    </TABLE>
     </BODY>
   </HTML>
   

So, even though the document consists of well-formed HTML, it's rendered as an XML document because we've forced the content type.

Non-XML Results

Being able to specify the content type comes in particularly handy when working with XML fragments in an XML-aware browser. As I mentioned earlier, executing a FOR XML query with no root element results in an error. You can, however, work around this by forcing the content to HTML, like this:

http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO&contenttype=text/html

If you load this URL in a browser, you'll probably see a blank page because most browsers ignore tags that they don't understand. However, you can view the source of the Web page and you'll see an XML fragment returned as you'd expect. This would be handy in situations where you're communicating with SQLISAPI using HTTP from outside of a browser—from an application of some sort. You could return the XML fragment to the client, then use client-side logic to apply a root element and/or process the XML further.

SQLISAPI also allows you to omit the FOR XML clause in order to return a single column from a table, view, or table-valued function as a plain text stream, as shown in Listing 18.31.

Listing 18.31

http://localhost/Northwind?sql=SELECT+CAST(CustomerId+AS+
  char(10))+AS+CustomerId+FROM+Customers+ORDER+BY+CustomerId
  &contenttype=text/html

(Results)

ALFKI ANATR ANTON AROUT BERGS BLAUS BLONP BOLID BONAP BOTTM BSBEV
CACTU CENTC CHOPS COMMI CONSH DRACD DUMON EASTC ERNSH FAMIA FISSA
FOLIG FOLKO FRANK FRANR FRANS FURIB GALED GODOS GOURL GREAL GROSR
HANAR HILAA HUNGC HUNGO ISLAT KOENE LACOR LAMAI LAUGB LAZYK LEHMS
LETSS LILAS LINOD LONEP MAGAA MAISD MEREP MORGK NORTS OCEAN OLDWO
OTTIK PARIS PERIC PICCO PRINI QUEDE QUEEN QUICK RANCH RATTC REGGC
RICAR RICSU ROMEY SANTG SAVEA SEVES SIMOB SPECD SPLIR SUPRD THEBI
THECR TOMSP TORTU TRADH TRAIH VAFFE VICTE VINET WANDK WARTH WELLI
WHITC WILMK WOLZA

Note that SQLISAPI doesn't support returning multicolumn results this way. That said, this is still a handy way to quickly return a simple data list.

Stored Procedures

You can execute stored procedures via URL queries just as you can other types of Transact-SQL queries. Of course, this procedure needs to return its result using FOR XML if you intend to process it as XML in the browser or on the client side. The stored procedure in Listing 18.32 illustrates.

Listing 18.32

CREATE PROC ListCustomersXML
@CustomerId varchar(10)='%',
@CompanyName varchar(80)='%'
AS
SELECT CustomerId, CompanyName
FROM Customers
WHERE CustomerId LIKE @CustomerId
AND CompanyName LIKE @CompanyName
FOR XML AUTO

Once your procedure correctly returns results in XML format, you can call it from a URL query using the Transact-SQL EXEC command. Listing 18.33 shows an example of a URL query that calls a stored procedure using EXEC.

Listing 18.33

http://localhost/Northwind?sql=EXEC+ListCustomersXML
  +@CustomerId='A%25',@CompanyName='An%25'&root=CustomerList

(Results)

<?xml version="1.0" encoding="utf-8" ?>
<CustomerList>
  <Customers CustomerId="ANATR" CompanyName="Ana Trujillo
      Emparedados y helados" />
  <Customers CustomerId="ANTON" CompanyName="Antonio Moreno
      Taquer'a" />
</CustomerList>

Notice that we specify the Transact-SQL wildcard character “%” by using its encoded equivalent, %25. This is necessary, as I said earlier, because % has special meaning in a URL query.