Download the Source
I've been working with XML templates more and more lately and I'm continuously amazed by how easy they are to use. This article covers what XML templates are and how to use them with SQL Server 2000.
A template is a valid XML document that can contain a root element, header,
parameters, and query statements. Every element within a template is optional,
except for the "sql" namespace, which must be defined (although it doesn't have
to be named "sql", you can rename this to any name you'd like).
Here's a simple template:
<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query>SELECT…FROM</sql:query>
</root>
|
Any of the following can be used with SQL XML templates:
- SELECT, INSERT, and UPDATE statements
- Stored Procedure Execution
- XML Schemas
- XML Data Reduced Schema (XDR)
- xPath Queries
- XSL Style Sheets
Using a Template to Retrieve Data
You can have one or more Transact-SQL queries within a template and retrieve
the data through a URL, all without exposing any of your underlying database
structure. Adding a query to a template is fairly straightforward. Simply wrap
your query statement within a <sql:query></sql:query>
tag:
| <sql:query>SELECT * FROM Customers FOR XML AUTO</sql:query> |
This example code selects customer data from the Northwind database and validates
the results against an XML customer schema.
To run the examples in the included demo, you'll need to configure SQL XML
support in Internet Information Server (IIS) and set up the nwind Virtual Directory
(both of these topics are explained in depth in SQL Books Online under XML and
Internet Support). To test your configuration from within the browser, type:
| http://<IISServer>/nwind?sql=SELECT * FROM Employees FOR XML AUTO&root=root |
Customers.xml
<?xml version="1.0" encoding="UTF-8"?>
<customerlist xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT * FROM Customers AS customer
FOR XML AUTO, ELEMENTS
</sql:query>
</customerlist>
|
The above query statement, SELECT * FROM Customers AS customer
FOR XML AUTO, ELEMENTS, returns a list of customers from Northwind as
XML and instructs SQL Server to format the results using XML elements instead
of the default, attributes. The first element within the document is <customerlist>
and is defined as the root for our template. There are two reasons for having
a root — for one, any XML document without a root is considered invalid;
and two, SQL Server only returns XML fragments when using FOR XML AUTO or FOR
XML RAW. If you were to execute SELECT * FROM Customers AS
customer FOR XML AUTO, ELEMENTS in Query Analyzer, you'd notice that
while several rows of XML data are returned, they are not contained within a
parent element.
Before you can execute a template, you must save it to a directory that is
associated with the virtual name of template type (refer to SQL Books Online
for more information on configuring SQL XML Support in IIS).
If the template is saved to a directory associated with a virtual name of template, the URL might look like this:
| http://<IISServer>/nwind/TemplateVirtualName/TemplateFile.xml |
To run the above example, copy and paste the code into a file named customers.xml,
save to C:\InetPub\wwwroot\nwind\template\, and run:
| http://<IISServer>/nwind/template/customers.xml |
Template Parameters
Templates can accept parameters in the same way as stored procedures. Similarly, you need to declare and name each parameter. Template parameters are specified with <sql:param name="myParameterName"/> and should be nested within a <sql:header></sql:header> tag.
Customers_param.xml
<?xml version="1.0" encoding="UTF-8"?>
<customerlist xmlns:sql="urn:schemas-microsoft-com:xml-sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<sql:header>
<sql:param name="customerID"/>
</sql:header>
<sql:query>
SELECT * FROM Customers AS customer
WHERE CustomerID = @customerID
FOR XML AUTO, ELEMENTS
</sql:query>
</customerlist>
|
To call this template and pass the customer ID parameter, run:
|
http://<IISServer>/nwind/template/customers_param.xml?customerID=ALFKI |
Executing this template returns information about the customer with an ID of
"ALFKI", the results of which can be seen in the following code:
<?xml version="1.0" encoding="UTF-8"?>
<customerlist xmlns:sql="urn:schemas-microsoft-com:xml-sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</customer>
</customerlist>
|
Validating Against an XML Schema
For the purposes of demonstration, I've created a basic XML schema to validate
our customer list (for more information on schemas, see the W3C's XML Schema
Primer at http://www.w3.org/TR/xmlschema-0/). The following code shows the Customers XML Schema (customer.xsd):
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:annotation>
<xsd:documentation>
Northwind customer schema
</xsd:documentation>
</xsd:annotation>
<xsd:element name="customerlist">
<xsd:complexType>
<xsd:sequence maxOccurs="unbounded">
<xsd:element ref="customer"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="customer" type="customerType"/>
<xsd:complexType name="customerType">
<xsd:sequence>
<xsd:element ref="CustomerID"/>
<xsd:element ref="CompanyName" minOccurs="0"/>
<xsd:element ref="ContactName"/>
<xsd:element ref="ContactTitle" minOccurs="0"/>
<xsd:element ref="Address"/>
<xsd:element ref="City" minOccurs="0"/>
<xsd:element ref="Region" minOccurs="0"/>
<xsd:element ref="PostalCode" minOccurs="0"/>
<xsd:element ref="Country" minOccurs="0"/>
<xsd:element ref="Phone" minOccurs="0"/>
<xsd:element ref="Fax" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
<!-- define elements -->
<xsd:element name="CustomerID" type="xsd:string"/>
<xsd:element name="CompanyName" type="xsd:string"/>
<xsd:element name="ContactName" type="xsd:string"/>
<xsd:element name="ContactTitle" type="xsd:string"/>
<xsd:element name="Address" type="xsd:string"/>
<xsd:element name="City" type="xsd:string"/>
<xsd:element name="Region" type="xsd:string"/>
<xsd:element name="PostalCode" type="xsd:string"/>
<xsd:element name="Country" type="xsd:string"/>
<xsd:element name="Phone" type="xsd:string"/>
<xsd:element name="Fax" type="xsd:string" />
</xsd:schema>
|
Now, we'll link the XML template to our schema through the defined 'xsi' namespace.
Customers_schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<customerlist xmlns:sql="urn:schemas-microsoft-com:xml-sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="customer.xsd">
<sql:header>
<sql:param name="customerID"/>
</sql:header>
<sql:query>
SELECT * FROM Customers AS customer
WHERE CustomerID = @customerID
FOR XML AUTO, ELEMENTS
</sql:query>
</customerlist>
|
The result is SQL Server data dynamically returned as a well-formed and valid XML document!
Conclusion
SQL Server 2000 and XML Templates are a powerful combination, providing a secure and easy way to access SQL Server data over HTTP.
In this article, we've created a template, embedded a SELECT statement, defined
and passed template parameters, and validated our result set against an XML
schema. That is only the beginning of what you can do with SQL XML templates.
Points to remember:
- Everything within a template is optional with the exception of the root
element's namespace, which must be defined.
- You can include multiple query statements within a template.
- You must include a root element within your template to return well-formed
XML.
The full source code for this article can be downloaded from this page as well as http://www.denisewhite.co.uk/articles/sqljunkies/xmltemplatesintro.zip.