7.6 SQLXML Templates

Templates are XML documents that provide a parameterized query and update mechanism to the database. Templates can contain either T-SQL statements, Updategrams, XPath queries, or a combination thereof. Examples for Updategrams and XPath queries are given in section 7.7. Listing 7.8 shows the basic structure of a parameterized template that retrieves a Customer-Order hierarchy for a specific region and applies an XSLT post-processing step that transforms the template result into another XML document or some other format (such as HTML).

Listing 7.8 Example of a Parameterized Template

<root xmlns:sql ="urn:schemas-microsoft-com:xml-sql"
      sql:xsl="CustOrd.xsl" >
  <sql:header nullvalue="NULL" >
    <sql:param name="City">%</sql:param>
    <sql:param name="state">WA</sql:param>
  </sql:header>
  <sql:query>
    SELECT Customers.CustomerID, OrderID
    FROM Customers LEFT OUTER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
    WHERE City LIKE @City
    AND Region LIKE @state
    ORDER BY Customers.CustomerID
    FOR XML auto
  </sql:query>
</root>

The optional sql:xsl attribute on the template root element points to the file that the server-side XSLT transformation will apply to the template results. The transformation will take place inside the OLEDB provider. The sql:header contains the parameter declarations. Each parameter declaration provides the name of the parameter and a default value. The optional attribute nullvalue declares that the specified string ("NULL" in Listing 7.8) will be interpreted as the NULL value if passed as the parameter value. The parameters will be referenced by name inside the queries using their native variable reference mechanism. sql:query can contain arbitrary T-SQL statements (best placed into a CDATA section to avoid problems with < or other special characters). In Listing 7.8, it is a FOR XML query. The result of the template before the application of the XSLT transform with the default parameter values may look like Listing 7.9.

Listing 7.9 Result of Template before XSLT Transform

<root xmlns:sql ="urn:schemas-microsoft-com:xml-sql" >
  <Customers CustomerID="LAZYK">
    <Orders OrderID="10482" />
    <Orders OrderID="10545" />
  </Customers>
  <Customers CustomerID="TRAIH">
    <Orders OrderID="10574" />
    <Orders OrderID="10577" />
    <Orders OrderID="10822" />
  </Customers>
</root>

Top