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

A safer and more widely used technique for retrieving data over HTTP is to use server-side XML templates that encapsulate Transact-SQL queries. Because these templates are stored on the Web server and referenced via a virtual name, the end users never see the source code. The templates are XML documents based on the XML-SQL namespace and function as a mechanism for translating a URL into a query that SQL Server can process. As with plain URL queries, results from template queries are returned as either XML or HTML.

Listing 18.34 shows a simple XML query template.

Listing 18.34

<?xml version='1.0' ?>
<CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
   <sql:query>
   SELECT CustomerId, CompanyName
   FROM Customers
   FOR XML AUTO
   </sql:query>
   </CustomerList>
   

Note the use of the sql namespace prefix with the query itself. This is made possible by the namespace reference on the second line of the template (bolded).

Here we're merely returning two columns from the Northwind Customers table, as we've done several times in this chapter. We include FOR XML AUTO to return the data as XML. The URL shown in Listing 18.35 uses the template, along with the data it returns.

Listing 18.35

http://localhost/Northwind/templates/CustomerList.XML

(Results abridged)

<?xml version="1.0" ?>
<CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customers CustomerId="ALFKI" CompanyName=
    "Alfreds Futterkiste" />
  <Customers CustomerId="VAFFE" CompanyName="Vaffeljernet" />
  <Customers CustomerId="VICTE" CompanyName=
    "Victuailles en stock" />
  <Customers CustomerId="VINET" CompanyName=
    "Vins et alcools Chevalier" />
  <Customers CustomerId="WARTH" CompanyName="Wartian Herkku" />
  <Customers CustomerId="WELLI" CompanyName=
    "Wellington Importadora" />
  <Customers CustomerId="WHITC" CompanyName=
    "White Clover Markets" />
  <Customers CustomerId="WILMK" CompanyName="Wilman Kala" />
  <Customers CustomerId="WOLZA" CompanyName="Wolski Zajazd" />
</CustomerList>

Notice that we're using the templates virtual name that we created under the Northwind virtual directory earlier.

Parameterized Templates

You can also create parameterized XML query templates that permit the user to supply parameters to the query when it's executed. You define parameters in the header of the template, which is contained in its sql:header element. Each parameter is defined using the sql:param tag and can include an optional default value. Listing 18.36 presents an example.

Listing 18.36

<?xml version='1.0' ?>
<CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
  <sql:header>
    <sql:param name='CustomerId'>%</sql:param>
   </sql:header>
   <sql:query>
   SELECT CustomerId, CompanyName
   FROM Customers
   WHERE CustomerId LIKE @CustomerId
   FOR XML AUTO
   </sql:query>
   </CustomerList>
   

Note the use of sql:param to define the parameter. Here, we give the parameter a default value of % since we're using it in a LIKE predicate in the query. This means that we list all customers if no value is specified for the parameter.

Note that SQLISAPI is smart enough to submit a template query to the server as an RPC when you define query parameters. It binds the parameters you specify in the template as RPC parameters and sends the query to SQL Server using RPC API calls. This is more efficient than using T-SQL language events and should result in better performance, particularly on systems with high throughput.

Listing 18.37 gives an example of a URL that specifies a parameterized template query, along with its results.

Listing 18.37

http://localhost/Northwind/Templates/CustomerList2.XML?
  CustomerId=A%25

(Results)

<?xml version="1.0" ?>
<CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customers CustomerId="ALFKI" CompanyName=
    "Alfreds Futterkiste" />
  <Customers CustomerId="ANATR" CompanyName=
    "Ana Trujillo Emparedados y helados" />
  <Customers CustomerId="ANTON" CompanyName=
    "Antonio Moreno Taquer'a" />
  <Customers CustomerId="AROUT" CompanyName="Around the Horn" />
</CustomerList>

Style Sheets

As with regular URL queries, you can specify a style sheet to apply to a template query. You can do this in the template itself or in the URL that accesses it. Here's an example of a URL that applies a style sheet to a template query:

http://localhost/Northwind/Templates/CustomerList3.XML?xsl=Templates/CustomerList3.xsl&contenttype=text/html

Note the use of the contenttype parameter to force the output to be treated as HTML (bolded). We do this because we know that the style sheet we're applying translates the XML returned by SQL Server into an HTML table.

We include the relative path from the virtual directory to the style sheet because it's not automatically located in the Templates folder even though the XML document is located there. The path specifications for a template query and its parameters are separate from one another.

As I've mentioned, the XML-SQL namespace also supports specifying the style sheet in the template itself. Listing 18.38 shows a template that specifies a style sheet.

Listing 18.38

<?xml version='1.0' ?>
<CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'
    sql:xsl='CustomerList3.xsl'>
  <sql:query>
    SELECT CustomerId, CompanyName
    FROM Customers
    FOR XML AUTO
  </sql:query>
</CustomerList>

The style sheet referenced by the template appears in Listing 18.39.

Listing 18.39

<?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><I>Customer ID</I></TD>
            <TD><I>Company Name</I></TD>
          </TR>
          <xsl:for-each select="CustomerList/Customers">
            <TR>
              <TD><B>
              <xsl:value-of select="@CustomerId"/>
              </B></TD>
              <TD>
              <xsl:value-of select="@CompanyName"/>
              </TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
  </xsl:template>
</xsl:stylesheet>

Listing 18.40 shows a URL that uses the template and the style sheet shown in the previous two listings, along with the results it produces.

Listing 18.40

http://localhost/Northwind/Templates/CustomerList4.XML?
    contenttype=text/html

(Results abridged)

Note that, once again, we specify the contenttype parameter in order to force the output to be treated as HTML. This is necessary because XML-aware browsers such as Internet Explorer automatically treat the output returned by XML templates as text/xml. Since the HTML we're returning is also well-formed XML, the browser doesn't know to render it as HTML unless we tell it to. That's what the contenttype specification is for—it causes the browser to render the output of the template query as it would any other HTML document.

Applying Style Sheets on the Client

If the client is XML-enabled, you can also apply style sheets to template queries on the client side. This offloads a bit of the work of the server but requires a separate roundtrip to download the style sheet to the client. If the client is not XML-enabled, the style sheet will be ignored, making this approach more suitable to situations where you know for certain whether your clients are XML-enabled, such as with private intranet or corporate applications.

The template in Listing 18.41 specifies a client-side style sheet translation.

Listing 18.41

<?xml version='1.0' ?>
<?xml-stylesheet type='text/xsl' href='CustomerList3.xsl'?>
   <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
   <sql:query>
   SELECT CustomerId, CompanyName
   FROM Customers
   FOR XML AUTO
   </sql:query>
   </CustomerList>
   

Note the xml-stylesheet specification at the top of the document (bolded). This tells the client-side XML processor to download the style sheet specified in the href attribute and apply it to the XML document rendered by the template. Listing 18.42 shows the URL and results.

Listing 18.42

http://localhost/Northwind/Templates/CustomerList5.XML?
    contenttype=text/html

(Results abridged)

Client-Side Templates

As I mentioned earlier, it's far more popular (and safer) to store templates on your Web server and route users to them via virtual names. That said, there are times when allowing the user the flexibility to specify templates on the client side is very useful. Specifying client-side templates in HTML or in an application alleviates the necessity to set up in advance the templates or the virtual names that reference them. While this is certainly easier from an administration standpoint, it's potentially unsafe on the public Internet because it allows clients to specify the code they run against your SQL Server. Use of this technique should probably be limited to private intranets and corporate networks.

Listing 18.43 presents a Web page that embeds a client-side template.

Listing 18.43

<HTML>
  <HEAD>
    <TITLE>Customer List</TITLE>
  </HEAD>
  <BODY>
    <FORM action='http://localhost/Northwind' method='POST'>
      <B>Customer ID Number</B>
      <INPUT type=text name=CustomerId value='AAAAA'>
      <INPUT type=hidden name=xsl value=Templates/CustomerList2.xsl>
      <INPUT type=hidden name=template value='
   <CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:header>
   <sql:param name="CustomerId">%</sql:param>
   </sql:header>
   <sql:query>
   SELECT CompanyName, ContactName
   FROM Customers
   WHERE CustomerId LIKE @CustomerId
   FOR XML AUTO
   </sql:query>
   </CustomerList>
   '>
   <P><input type='submit'>
   </FORM>
   </BODY>
   </HTML>
   

The client-side template (bolded) is embedded as a hidden field in the Web page. If you open this page in a Web browser, you should see an entry box for a Customer ID and a submit button. Entering a customer ID or mask and clicking Submit Query will post the template to the Web server. SQLISAPI will then extract the query contained in the template and run it against SQL Server's Northwind database (because of the template's virtual directory reference). The CustomerList2.xsl style sheet will then be applied to translate the XML document that SQL Server returns into HTML, and the result will be returned to the client. Listing 18.44 shows an example.

Listing 18.44

Customer ID Number

(Results)

As with server-side templates, client-side templates are sent to SQL Server using an RPC.