Leveraging SQL Server SQLXML Templates Part 1

							Leveraging SQL Server SQLXML Templates Part 1
Dan Brown, Dunstan Thomas Consulting
http://consulting.dthomas.co.uk



Summary

Traditionally when working with XML in a multi-tier architecture we have always had to write an
intermediate layer that iterates through an ADO Recordset’s columns and rows to build up an XML
document. More recently ADO.NET and its rich XML support have made this a simpler process by
allowing the population of an ADO Dataset and then using the ReadXML method to generate an
XmlDocument.

SQL Server 2000 and SQLXML 3 however, also offers comprehensive support for XML. So much so that
we now have the ability to skip this intermediate data translation layer and work with XML directly in our
web or .NET applications.

In this article I shall illustrate how we can leverage SQLXML templates and Internet Information Services
(IIS) without having to write an intermediate data translation layer. We shall see how to execute a Transact
SQL statement in a XML template and render it on the fly using a XSL template to produce a dynamic web
page report. In the second part of this article I shall demonstrate how we can convert our employees report
into a web based employees editor using HTML forms posting back to SQLXML updategram templates.

SQL Server 2000 SQLXML

In the of the shelf version of SQL Server 2000 you now have support for two new keywords; FOR XML
and OPENXML. With SQLXML 3.0 installed (available from Microsoft’s SQL Server download website)
you also have support for SOAP, XPath Queries, Updategrams, Template Queries, XML Bulk Load and
XSD.

Therefore you can now query SQL Server over HTTP to return relational data as XML, which can then be
queried using XPath. Alternatively and as illustrated in this article you can embed SQL Server queries and
stored procedures into an XML document which can then be later transformed using XSL templates to
provide a rich and efficient data rendering experience.

FOR XML

FOR XML is a new reserved word that has been added to SQL Server 2000 that allow us to produce an
XML representation of relational data. When using FOR XML, by default, elements map to a table or view,
while attributes map to columns. FOR XML can be used in a variety of ways depending on the XML
output structure required. The basic syntax of FOR XML is illustrated in Figure 1.

  FOR XML {RAW | AUTO | EXPLICIT} [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

Figure 1: Basic FOR XML Syntax

 Member                Description
 RAW                   Each row is represented in the <Row> element
 AUTO                  Results are retuned as a nested XML tree with the structure specified in the select
                       statement.
 EXPLICIT              Results are retuned as a nested XML tree with the structure explicitly specified
 XMLDATA               Returns an inline XDR schema with the results
 ELEMENTS              When specified all selected columns are returned as elements instead of attributes

Dan Brown, Dunstan Thomas Consulting                                       http://consulting.dthomas.co.uk
 BINARY BASE64        Binary Data will be returned using BASE 64 encoding.
Figure 2: FOR XML Arguments

Using the SQL Server database Northwind, if we were to run the query in Figure 3 we would have the
illustrated tabular data returned.

  SELECT EmployeeID, FirstName, LastName, Title
  FROM Employees




Figure 3: Traditional Select Query

However if we now add FOR XML to our query as shown we can now see the XML output.

  SELECT EmployeeID, FirstName, LastName, Title
  FROM Employees
  FOR XML AUTO




Figure 4: FOR XML AUTO Select Query

FOR XML AUTO Returns the following XML with each field as an attribute of an Employees element:

<Employees EmployeeID="1" FirstName="Nancy" LastName="Davolio" Title="Sales Representative"/>
<Employees EmployeeID="2" FirstName="Andrew" LastName="Fuller" Title="Vice President, Sales"/>
…

Alternatively we can return columns as elements using the ELEMENTS member shown in Figure 5 to
produce the XML shown in Figure 6:

  SELECT EmployeeID, FirstName, LastName, Title
  FROM Employees
  FOR XML AUTO, ELEMENTS

Figure 5: FOR XML AUTO, ELEMENTS Select Query


Dan Brown, Dunstan Thomas Consulting                                    http://consulting.dthomas.co.uk
  <Employees>
    <EmployeeID>1</EmployeeID>
    <FirstName>Nancy</FirstName>
    <LastName>Davolio</LastName>
    <Title>Sales Representative</Title>
  </Employees>
  <Employees>
    <EmployeeID>2</EmployeeID>
    <FirstName>Andrew</FirstName>
    <LastName>Fuller</LastName>
    <Title>Vice President, Sales</Title>
  </Employees>

Figure 6: FOR XML AUTO, ELEMENTS XML output

In all these cases the XML returned as a result of a FOR XML query does not contain a root element, this
as shown in Figure 7 can be added directly to the Transact SQL Script to make the XML well formed.

  SELECT '<root>'
  SELECT EmployeeID, FirstName, LastName, Title
  FROM Employees
  FOR XML AUTO, ELEMENTS
  SELECT '</root>'

Figure 7: FOR XML AUTO, ELEMENTS with Root Node

Querying SQL Server over HTTP

Now we are familiar with returning data as XML output we can leverage SQL Server 2000’s ability to
query a database via HTTP by utilising IIS virtual directories. This allows XML to be passed over remote
disparate platforms while reducing the complexity and need for intermediate layers to produce XML and
provide error handling.

In order to do this we need to configure XML support in IIS using the Microsoft Management Console
(MMC) snap-in found in the SQL Server 2000 program group. For the purposes of my working example
we will set-up HTTP access to the Northwind database.

Firstly we need to create a new virtual directory using the “Configure SQL XML Support in IIS” snap-in to
create an alias to our database and directories.

    •   General Tab – The first figure below shows the settings for the general tab, the virtual directory I
        have named northwind since we will be connecting to that database and I have created a directory
        called northwindxml inside the inetpub\wwwroot directory that will store our files.
    •   Security Tab – Select the appropriate authentication method in the security tab, I have used
        “Windows Integrated Authentication”.
    •   Data Source Tab – Specify the SQL Server Instance and database name. For the purposes of my
        example I have used (Local) and Northwind.
    •   Settings Tab – SQL Sever 2000 provides a number of methods for querying data, either directly
        in the URL, using Templates, XPath and a number of methods for writing XML documents back
        to the database using updategrams and OPENXML. As there are a number of security issues
        relating to querying the database in the URL and the context of my example is read-only, I have
        chosen to use templates for this example. Therefore I have only checked the templates option as
        shown in Figure 10 below.
    •   Virtual Names Tab – SQL Server allows us to query database objects, templates and schemas
        directly from a URL. In order to do so we need to assign a virtual name to the type of object we

Dan Brown, Dunstan Thomas Consulting                                       http://consulting.dthomas.co.uk
        are querying and a logical path. For the purposes of my example, I have created the alias
        “template” of type “template” that will point to our C:\Inetpub\wwwroot\northwindxml directory.
    •   Advanced Tab – I have used the defaults.




Figure 8: General Tab                               Figure 9: Data Source Tab




Figure 10: Settings Tab                             Figure 11: Virtual Names Tab




Dan Brown, Dunstan Thomas Consulting                                    http://consulting.dthomas.co.uk
Creating an XML Query Stored Procedure

Now we have an IIS Virtual directory pointing to our Northwind database we can start writing some code. I
have decided to embed my FOR XML SQL in the stored procedure shown in Figure 12 below; this makes
maintenance a lot easier and keeps our database code within our database.

  CREATE PROCEDURE dbo.spGetEmployessXml(
     @LastName VARCHAR(20) = '%'
  )
  AS
  BEGIN
     SELECT EmployeeID, FirstName, LastName, Title
     FROM Employees
     WHERE LastName LIKE (@LastName)
     FOR XML AUTO
  END

Figure 12: FOR XML Stored Procedure

I have also added the optional parameter @LastName so we can refine the results if we wish or simply
select all employees by omitting the parameter when we call the stored procedure. Notice that I have not
added a root element, this I shall specify in my XML template in the next section.


Creating an XML Template

Now we create an XML document that I have named employees.xml and save it to the
C:\Inetpub\wwwroot\northwindxml directory. Here I have created a root node called root and referenced
the required namespace declaration xmlns:sql="urn:schemas-microsoft-com:xml-sql" which is the
attribute mapping schema for SQL XML.

The elements <header>, <param> and <query> are optional and can be used as needed. Also an XSL
stylesheet can be referenced using the sql:xsl attribute, however I my example I have chosen to pass in the
XSL style sheet template as a parameter to allow for maximum flexibility, therefore this attribute has been
omitted from the root node in my example shown in Figure 13.

  <?xml version="1.0" encoding="UTF-8"?>
  <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
          <sql:param name="LastName">%</sql:param>
      </sql:header>
      <sql:query>
       EXEC [Northwind].[dbo].[spGetEmployessXml] @LastName
      </sql:query>
  </root>

Figure 13: Contents of c:\Inetpub\wwwroot\northwindxml\employees.xml

Creating an XSL stylesheet template

Now we have created our xml data source template we need to create an XSL template to render the XML
into a readable and useful webpage. In order to do this I shall create one XSL template to transform our xml
to a HTML report and one cascading stylesheet to store our data formatting preferences. I shall name the
XSL template employees_list.xsl shown in Figure 14 below and save it to the northwindxml directory.




Dan Brown, Dunstan Thomas Consulting                                       http://consulting.dthomas.co.uk
 <?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:template match="/">
          <HTML>
              <HEAD>
                 <!-- Reference our css style sheet in the C:\Inetpub\wwwroot\northwindxml directory -->
                 <LINK rel="stylesheet" type="text/css" href="http://localhost/northwindxml/employees.css"/>
                 <TITLE>Employees</TITLE>
              </HEAD>
              <BODY>
                 <TABLE>
                      <!-- Iterate root/Employees nodes -->
                      <xsl:for-each select="root/Employees">
                      <!-- Sort by FirstName -->
                      <xsl:sort select="@FirstName" order="ascending"/>
                           <!-- If this is the first row then output a table header -->
                           <xsl:if test="position()=1">
                                <TR>
                                     <TH colspan="4">
                                     Employees
                                     </TH>
                                </TR>
                                <TR>
                                     <TH>EmployeeID</TH>
                                     <TH>First Name</TH>
                                     <TH>Last Name</TH>
                                     <TH>Title</TH>
                                </TR>
                           </xsl:if>
                           <TR>
                                <!-- Output alternating cell background colour for each detail row -->
                                <xsl:choose>
                                     <xsl:when test="(position() mod 2) = 0">
                                          <xsl:attribute name="class">Row1</xsl:attribute>
                                     </xsl:when>
                                     <xsl:otherwise>
                                          <xsl:attribute name="class">Row2</xsl:attribute>
                                     </xsl:otherwise>
                                </xsl:choose>
                                <TH>
                                     <xsl:value-of select="@EmployeeID"/>
                                </TH>
                                <TD>
                                     <xsl:value-of select="@FirstName"/>
                                </TD>
                                <TD>
                                     <xsl:value-of select="@LastName"/>
                                </TD>
                                <TD>
                                     <xsl:value-of select="@Title"/>
                                </TD>
                           </TR>
                      </xsl:for-each>
                 </TABLE>
              </BODY>
          </HTML>
     </xsl:template>
 </xsl:stylesheet>




Dan Brown, Dunstan Thomas Consulting                                          http://consulting.dthomas.co.uk
Figure 14: employees_list.xsl template
This XSL template references the employees.css stylesheet that I have created (See Figure 15) and saved to
our northwindxml directory. The XSL template will then perform an ascending sort on the FirstName field
and iterate through each of the Employees elements. When the first element is found a table header row
will be output, then for each subsequent element I test for every other row so that I can switch the
background colour and then output each element using standard HTML table tags.

All the specific formatting has been stored in the cascading stylesheet employees.css shown below which
saves editing the XSL template when we want to change the style or font colour for example.

  <STYLE>
  TH
  {
    FONT-WEIGHT: bolder;
    FONT-SIZE: 9pt;
    COLOR: white;
    FONT-FAMILY: Verdana;
    HEIGHT: 18pt;
    BACKGROUND-COLOR: darkblue
  }
  .Row1
  {
    FONT-SIZE: 9pt;
    COLOR: black;
    FONT-FAMILY: Verdana;
    BACKGROUND-COLOR: deepskyblue
  }
  .Row2
  {
    FONT-SIZE: 9pt;
    COLOR: black;
    FONT-FAMILY: Verdana;
    BACKGROUND-COLOR: powderblue
  }
  BODY
  {
    COLOR: white;
    BACKGROUND-COLOR: gray;
    TEXT-ALIGN: center
  }
  </STYLE>


Figure 15: employees.css style sheet
To briefly re-cap we have performed a number of steps to get this far:

    1.   Configured IIS for HTTP access to templates only on the northwind database
    2.   Create SQL Server Stored Procedure to select our data as XML.
    3.   Create an XML template to execute our stored procedure and return the XML document.
    4.   Create an XSL template to transform our XML document into HTML
    5.   Create a CSS stylesheet to format our HTML.

Now we can test our employee’s webpage by typing the following URL in our browser to produce the
dynamic HTML output shown in Figure 18. Here the URL is built up with the following:

Element                     Description
Localhost                   Local IP Name
Northwind                   Alias specified in the general tab of “Configure SQL XML Support in IIS”
                            MMC snap-in


Dan Brown, Dunstan Thomas Consulting                                      http://consulting.dthomas.co.uk
Template                   Template alias specified in the virtual names tab of the MMC snap-in.
Employees.xml              Our XML Template saved at c:\inetpub\wwwroot\northwindxml
xsl= employees_list.xsl    Parameter specifying our XSL template.
contenttype=text/html      Optional Parameter specifying the output type
Figure 16: URL Elements

The resulting URL:

  http://localhost/northwind/template/employees.xml?xsl=employees_list.xsl&contenttype=text/html

Figure 17: URL To retrieve our employee’s xml and transform to html

The final web page:




Figure 18: Final employees output

We can even add another parameter to our URL specifying the surname(s) of the employees to retrieve. So
for example the URL shown in Figure 19 will return all the employees with the surname ‘King’.

  http://localhost/northwind/template/employees.xml?xsl=listemployees.xsl&contenttype=text/html?Last
  Name=King

Figure 19: URL to retrieve Robert King




Dan Brown, Dunstan Thomas Consulting                                     http://consulting.dthomas.co.uk
Building on this example

In the second part of this article I shall build on this and make our employees report editable by creating a
new edit XSL template outputting HTML forms that post their values back to a SQLXML template
updategrams.

Conclusion

We have seen just how easy it is to leverage SQL Server SQL XML in a way that for simple web
applications we no longer require an intermediate translation layer when working with XML documents.

Using FOR XML we have seen how we can execute Transact SQL code in an XML document and render it
on the fly using a supplied XSL template to create a dynamic employees report. In the second part of this
article I shall illustrate how we can use SQLXML updategram templates and forms to turn our employees
report into an employee’s editor.

Further Reading

SQL Server 2000 Books Online, search in index for “XSL”, “OPENXML”, “XSLDemo sample” and
“XSLStartup sample”.

MSDN - SQLXML Virtual Directory Structure - Drew Minkin, Microsoft Corporation.

SQL Server 2000 and XML - Kirk Evans, Ashwin Kamanna, Joel Mueller – Informit.com

SQL XML and MSXML - Darshan Singh, PerfectXML.com.




Dan Brown, Dunstan Thomas Consulting                                        http://consulting.dthomas.co.uk