We can also specify the root element for our XML data, thus eliminating the need to specify the Root keyword in the URL.

Templates also provide security. When we use templates the query string, or stored procedure name, is stored in the template and not exposed in the URL of the browser. This hides the details of our columns and tables that were exposed when we included a SELECT statement in the URL.

As we mentioned above, templates are XML documents, thus they must conform to the XML standards and be well-formed. Also all elements must conform to the XML standards set out by the W3C.

Let's take a look at a simple template. This template starts with the <?XML?> element, identifying it as an XML document. This ensures that the browser parses the document correctly:

<?xml version="1.0" encoding="UTF-8"?>
<Employees xmlns:sql="urn:schemas-microsoft-com:xml-sql"
  sql:xsl="../Employee.xsl"> 
  <sql:query>
   SELECT First_Name_VC, Last_Name_VC FROM Employee_T 
     FOR XML AUTO 
  </sql:query>
</Employees>

The second line of code in this template specifies the Root element and we have named it Employees. The namespace, which is required, is xmlns:sql="urn:schemas-microsoft-com:xml-sql". A namespace specifies a collection of names that can be used as element or attribute names in your XML document. This namespace describes the data in this XML document as SQL Server XML data.

The sql:xsl attribute specifies the XSL stylesheet that should be used. Our template resides in the Template directory, which is a sub-directory of the htData directory. Our XSL stylesheet resides in the root of the htData directory. Given this, we specify two consecutive periods and then a forward slash before the XSL stylesheet name. This ensures that IIS goes up one directory to look for the Employee.xsl stylesheet.

The third element defined in the template, <sql:query>, specifies the query string to be executed. The query string can consist of SQL statements or a stored procedure name. We then have the closing tags for the <sql:query> element and the root element of <Employees>. If you were to save this template as Employee.xml in the Template directory of the htData directory you could execute this template using the following URL:

http://localhost/htData/Template/Employee.xml?ContentType=Text/HTML

Notice that the amount of data that needs to be entered into the URL has been reduced by the use of the template. Here we have specified the machine name that IIS is running on, followed by our virtual directory name and the directory that contains our template, before finally identifying our template name.

To have the results displayed as formatted XML, we have specified the ContentType keyword. (If this keyword is not specified, the resulting XML will be returned as raw XML.)

The results that would be displayed are shown below:

Try It Out - Hardware XML Template

Let's create a template to execute our up_select_xml_hardware stored procedure and use the Hardware.xsl stylesheet to format the results.

1. The code below shows the Hardware.xml template. Enter this code using your favorite text editor and save the file in the Template directory as Hardware.xml. (The Template directory is a sub-directory of the htData directory.)

<?xml version="1.0" encoding="UTF-8"?>
<Hardware xmlns:sql="urn:schemas-microsoft-com:xml-sql"
  sql:xsl="../Hardware.xsl">  
  <sql:query>  
   EXECUTE up_select_xml_hardware
  </sql:query> 
</Hardware>

2. To execute this XML template, enter the following URL in your browser. This URL specifies that the Hardware.xml template should be executed and that this template resides in the Template directory in the htData virtual directory. We have specified the ContentType keyword to have the results formatted as HTML.

http://localhost/htData/Template/Hardware.xml?ContentType=Text/HTML

The results of the execution of this template are shown in the following figure. Notice that the results of the execution are formatted using the Hardware.xsl stylesheet that we specified in our template:

How It Works - Hardware XML Template

We start this template off with the standard XML declaration. Then we specify the root element and give it a name of Hardware. We specify the standard SQL namespace of xmlns:sql="urn:schemas-microsoft-com:xml-sql" and then include the sql:xsl attribute to point to the Hardware XSL stylesheet. Notice that since this stylesheet resides in the root of the htData directory we have specified two periods followed by a forward slash to indicate that IIS should go up one directory level from where the template resides to find the stylesheet.

<?xml version="1.0" encoding="UTF-8"?>
<Hardware xmlns:sql="urn:schemas-microsoft-com:xml-sql"
  sql:xsl="../Hardware.xsl">  

We then specify the <sql:query> element, the EXECUTE statement, and then the stored procedure name to be executed.

We then terminate the <sql:query> element and the root element.

  <sql:query>
   EXECUTE up_select_xml_hardware
  </sql:query>
</Hardware>

Continued...