• Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Doing XML with T-SQL

Rick Dobson

In this article, Rick Dobson gives you a quick and useful intro to using XML templates. As you read it, you may also want to read or re-read Rick's January 2004 article on using the Web Services Toolkit to create Web Services and their clients, Tom Moreau's March 2001 column on feeding XML to stored procedures, and the late Anton Britten's February 2002 article on "getting the XML back in."

Have you noticed how many of the "hot topics" of the past few years require radically different skill sets than those familiar to the typical SQL Server DBA? For example, wouldn't it be nice if we could leverage our existing skills, such as writing T-SQL, to return query results as XML documents over the Web? Well, Microsoft probably had this in mind when it designed the feature set for XML template technology. This technology initially shipped with SQL Server 2000, and it has been tweaked slightly in subsequent Web Releases. With this technology, browser clients can invoke T-SQL statements that are embedded in XML templates within a virtual directory and have the XML results displayed. The templates can accept parameter values, and you can even allow clients to dynamically update a database. The XML syntax for templates is very simple, and the T-SQL is, of course, familiar.

	Since XML is tag-delimited text, it's easy for clients to read XML in a Web browser. If your application requires more traditionally formatted output, you can apply XSLT (XSL Transformations) to convert your XML result set into an HTML table or some other layout, for example. In this article, I'll show you how to use this technology with SQLXML 3.0 SP1 for SQL Server 2000. [SQLXML is the technology that allows client programming support for SQL Server, as opposed to the optional FOR XML clause available for server programming.–Ed.] If you don't have the latest version, you can download it from www.msdn.com/sqlxml.

Quick review of XML templates

XML templates rely on three SQL Server 2000 features. First, the templates must reside in a special virtual IIS directory that points at a SQL Server database. Second, you must append a FOR XML clause to any T-SQL statement that returns results. (This clause lets you specify the format for the results as well as where the XML formatting of the result set is to take place.) Third, you need to embed T-SQL statements in an XML document–the XML template.

	SQL Server 2000 initially shipped with the IIS Virtual Directory Management client tool, but subsequent Web Releases modified the tool's functionality. To get started, choose Start | SQLXML 3.0 | Configure IIS Support. I used the tool to create a directory named SPTemplates with a nested folder named MyTemplates that had a template type, and the SPTemplates directory pointed to the sample pubs database. (You can assign any arbitrary name to a virtual directory, but the directory must contain a template type folder if you're going to use XML templates.) Unless you designate Windows integrated authentication, all users will have to log in with the same SQL Server login. However, you can control the permissions for this login as well as Windows logins with traditional SQL Server security settings.

	The FOR XML clause appends to the end of many common T-SQL query statements. Three common arguments for the clause include RAW, AUTO, and NESTED. RAW is adequate when you return query results based on a single table; the returned XML document will include a row element for each row of column values, which appear as attribute values. The AUTO and NESTED arguments are appropriate for query results based on two or more tables, as well as for queries based on views. Either argument can show nested results with tags that denote their source, as you'll see later in the article.

	The NESTED argument is only legitimate when you indicate client-side formatting for the query results. Client-side formatting can improve scalability when retrieving results from a heavily loaded SQL Server. When you designate the NESTED argument with client-side formatting, your query statements can contain the GROUP BY clause and aggregate functions, which are not permitted with the RAW and AUTO arguments.

	You can specify multiple XML template queries within the template folder for a given virtual directory, and you can also embed database maintenance statements, such as INSERT and DELETE. Each XML template query must have a unique root tag within its XML document, and the root tag must contain a reference to the urn:schemas-microsoft-com:xml-sql namespace, which defines special template elements, such as query, header, and param. You represent an element with its opening and closing tags; T-SQL statements are embedded within opening and closing query tags. You can designate client-side formatting with an attribute for the query tag, and if your T-SQL statements reference parameters, you can declare them with param tags that you embed in header tags.

Returning query results

Listing 1 shows the contents of the ListAuthors.xml file, a template query that returns the first and last names of all the authors by last name from the authors table. Because my SPTemplates virtual directory points to the pubs database, the query refers to the authors table in that database. In Listing 1, the root tag designates the namespace with element definitions for XML template queries. (You're free to use any unique name for the root tag in place of ROOT.)

Listing 1. A template query for selected columns from the authors table.

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:query>
        SELECT au_fname, au_lname 
        FROM authors 
        ORDER BY au_lname 
        FOR XML AUTO
      </sql:query>
</ROOT>

&#9;Figure 1 shows the browser window with the query results from ListAuthors.xml. The Address box shows the URL for invoking the template query in the MyTemplates folder of the SPTemplates virtual directory on an IIS server named cablat. Because I used the AUTO argument in Listing 1's FOR XML clause, each row appears with the name of the table serving as the source for the query. If I'd used RAW in lieu of AUTO, then row would replace authors in each row of the result set.

&#9;In many applications, you need to let users fine-tune the contents of a result set at runtime, and parameters are a common way to implement this functionality. The ListSearchedAuthors.xml template query in Listing 2 will allow a user (hold your breath!) to designate an author's last name at runtime. If the user doesn't specify a last name, the query returns a default for authors with a last name of Bennet. The param tag allows you to specify a parameter for a template query. All you have to do is use the name attribute for the param tag to designate a parameter's name and then precede that name with an @ symbol in the T-SQL for the query. The value between the opening and closing param tags denotes the default parameter value.

Listing 2. A template query demonstrating the syntax for parameters.

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
        <sql:param name='searchname'>Bennet
        </sql:param>
      </sql:header>
      <sql:query>
        SELECT au_fname, au_lname
        FROM authors
        WHERE au_lname = @searchname
        ORDER BY  au_lname
        FOR XML AUTO
      </sql:query>
</ROOT>

&#9;Figure 2 shows the result of invoking the ListSearchedAuthors.xml template query. Users can specify a parameter value by trailing the URL for the template query with a question mark (?) followed by a pair of values delimited from one another by an equal sign (=). The first entry in the pair is the parameter name; the second is the parameter value. Since there are two authors with a last name of Ringer, the result set includes two rows starting with the authors tag.

&#9;The next listing shows a more sophisticated template query called ListTitlesForAuthors.xml. The T-SQL statement in this query joins three tables to show the book titles for an author. Users can specify the author they're interested in by specifying an author's last name as a parameter, and, again, the query uses Bennet as the default. The ListTitlesForAuthors.xml in Listing 3 shows the syntax for implementing these specifications as well as for designating client-side formatting. First, it assigns a value of 1 to the client-side-xml attribute. Then, it designates NESTED as the FOR XML argument.

Listing 3. A template query with a parameter and client-side formatting.

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
        <sql:param name='searchname'>
        Bennet</sql:param>
      </sql:header>
      <sql:query client-side-xml="1">
        SELECT a.au_fname, a.au_lname, t.title
        FROM authors a JOIN titleauthor ta
        ON a.au_id = ta.au_id 
        JOIN titles t
        ON ta.title_id = t.title_id
        WHERE au_lname=@searchname
        FOR XML NESTED
      </sql:query>
</ROOT>

&#9;Figure 3 shows the results of invoking ListTitlesForAuthors.xml for author last name Ringer. You can see that the two Ringers (no pun intended) collaborated on one title, but each also wrote another book on his or her own. The query results from the titles table appear nested within each row from the authors table.

Updating a database with a template query

In addition to returning result sets as XML documents in a browser, XML template queries permit you to perform database maintenance tasks with T-SQL keywords. In Listing 4, the InsertTitleAuthor.xml template query enters a row in each of three tables–the titles, authors, and titleauthor tables. Notice that the listing uses the INSERT keyword to specify selected column values for a new row. In addition, the sample code demonstrates the use of multiple parameters in a single template query. (A comment line marks the beginning of the parameter declarations.) Each parameter has a default value, but users can override these when they invoke the template query.

Listing 4. A template query for inserting a new row in the titles, authors, and titleauthor tables.

  <ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<!-- Define parameters and defaults -->
<sql:param name=
    'in_au_id'>123-45-6789</sql:param>
<sql:param name='in_au_fname'>Rick</sql:param>
<sql:param name='in_au_lname'>Dobson</sql:param>
<sql:param name='in_title_id'>PC1234</sql:param>
<sql:param name=
    'in_title'>Database Development</sql:param>
</sql:header>
<sql:query client-side-xml="1">
   INSERT INTO titles (title_id, title)
   VALUES (@in_title_id, @in_title)
   INSERT INTO authors 
       (au_id, au_fname, au_lname, contract)
   VALUES 
       (@in_au_id, @in_au_fname, @in_au_lname, 1)
   INSERT titleauthor (au_id, title_id)
   VALUES (@in_au_id,@in_title_id)
</sql:query>
</ROOT>

&#9;When inserting rows, it will be common to have multiple parameters. The syntax for specifying multiple parameters is the same as for a single parameter, except that you delimit name-value pairs from one another with an ampersand (&). The default settings for the InsertTitleAuthor.xml template query designate author first name and last name values of Rick and Dobson. Users can override these default settings with custom ones of Rickie and Dobs, for example, by entering the following in a browser's Address box:

  http://cablat/SPTemplates/MyTemplates/
InsertTitleAuthor.xml?in_au_fname=Rickie&amp; 
in_au_lname=Dobs

&#9;Deleting rows from a table is a bit easier to code. All you need to do is specify the row or rows to which you want to apply the DELETE keyword, and you can do this by using row primary key values as arguments for any DELETE statements' WHERE clauses. The DeleteTitleAuthor.xml template query in Listing 5 deletes a row from the titleauthor table. After removing the foreign key reference from the titleauthor table, the query removes one row each from the authors and the titles tables.

Listing 5. A template query for deleting a row from the titleauthor, authors, and titles tables.

  <ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name= 
  'in_au_id'>123-45-6789</sql:param>
<sql:param name=
  'in_title_id'>PC1234</sql:param>
</sql:header>
<sql:query client-side-xml="1">
    DELETE FROM titleauthor
    WHERE au_id = 
        '123-45-6789' and title_id = 'PC1234'
    DELETE FROM authors
    WHERE au_id = '123-45-6789'
    DELETE FROM titles
    WHERE title_id = 'PC1234'
</sql:query>
</ROOT>

Conclusions

XML template queries are an easy and fast way to generate XML output in a browser. While you can populate template queries with other contents besides T-SQL, SQL Server professionals will appreciate being able to leverage their T-SQL skills to generate XML. In addition to creating XML-formatted contents for a browser, you can use template queries to manipulate the values in a SQL Server database. You've also seen how to make both data access and data manipulation tasks dynamic at runtime by using parameters. Experiment!

Download 407RICK.TXT

See "XML Support in Microsoft SQL Server 2005" at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp.

  • XML is a native data type, optionally constrained by an XML schema (in which case it's called typed XML). Untyped XML is good if you don't know the schema a priori or if it's complex. XML columns can also be constrained by most T-SQL constraints, but not Unique, PK, or FK.
  • XML values (up to 2GB/instance) are stored internally as BLOBs.
  • The XML hierarchy is limited to 128 levels.
  • You can create indexes on typed and untyped XML data columns using a new DDL statement. The first index on an XML column, which requires a clustered index on the PK column, is the primary index–a B+tree index on all tags, values, and paths of the XML instances in the column. Secondary XML indexes can be created for PATH, PROPERTY, and VALUE.
  • SET SHOWPLAN_XML ON allows an XML showplan to be delivered as a single-value result set.
  • XML schemas are optional, but when defined (CREATE XML SCHEMA COLLECTION), the XML schema collection is stored as part of system metadata, with nodes as Unicode.
  • There's a new built-in function: XML_SCHEMA_NAMESPACE().
  • XPathDocument replaces XmlDocument as the primary XML store.
  • There are CLR type accessors for XML data.
  • A subset of XQUERY is supported. XQUERY–think FLWOR (For/Let/Where/OrderBy/Return).
  • There are five XML data type methods: query(), value(), exist(), nodes(), and modify().

–kw

To find out more about SQL Server Professional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the July 2004 issue of SQL Server Professional. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.