XML Templates

In previous chapters we saw that our SELECT statements have the potential to become very large, so we can also see the potential for our stored procedure statements to become large. This increases as we add more and more parameters, especially if we use string parameters such as an employee name. All of this leads to more data that needs to be entered into the URL of the browser.

 

To help reduce the amount of data that needs to be entered into the URL we can use XML templates. XML templates are simply XML files that contain the query string or stored procedure to be executed. 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:

 

 

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:

 

 

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:

 

 

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.)

 

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.

 

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.

 

 

 

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.

                         

XML Templates that Accept Parameters

We know that we can create a template that executes a SQL string and we can also create a template that executes a stored procedure. We have seen how using templates cuts down on the amount of text that needs to be included in a URL to get the XML data that we want.

 

Now let's examine how to create a template that executes a stored procedure that accepts parameters. When we create a template that executes these stored procedures, we must define those parameters in the template also. This is because we will pass the parameters to the template when we execute it. The XML inside the template will in turn pass these parameters to the stored procedure being executed.

 

In order to define parameters in a template we must include the <sql:header> element. Within this element we define one <sql:param> element for each parameter that our stored procedure expects. The <sql:param> element has a name attribute that we use to assign the name of the parameter. If we want, we can even specify a default value for the parameter.

 

The following code fragment shows the parameters for the up_parmsel_employee stored procedure. This stored procedure does not actually exist and is used for illustration purposes only. Notice that we have specified a default value for each parameter between the beginning <sql:param> tag and the closing tag for this element:

 

 

Let's assume the completed template has been saved with a name of EmployeeLocation.xml. We would then be able to execute this template without any parameters by specifying the following URL. This URL would cause the default values defined in our template to be passed to the stored procedure.

 

 

Assuming we now wanted to execute this same template and pass it some parameters that were in turn to be passed to the stored procedure, we would then specify a URL such as the one shown below. Here we have listed the parameter names and values:

 

 

 

Since we now know how to create parameters within a template, let's put this knowledge to use. The template that we want to create now will execute the up_parmsel_xml_hardware stored procedure. Since this stored procedure accepts one input parameter we will define one parameter in our template.

 

1.       The code for the SystemSpecs.xml template is listed below. Create this template and save it in the Template directory of the htData virtual directory:

 

 

2.       To execute this template enter the following URL in your browser. You will need to substitute the value for the Hardware_ID parameter with a valid hardware ID from your Hardware_T table.

 

The results you see should resemble the results shown in the next figure. This template uses the SystemSpecs.xsl stylesheet to format the results:

 

How It Works – XML Template with Parameters

We start this template with the standard XML declaration. Then we include the Hardware root element, which contains the SQL namespace and the XSL stylesheet to be used to format the XML data:

 

 

Next, we include the <sql:header> element. Within this element we define the <sql:param> element and set its name attribute to the parameter name in our stored procedure. We have not specified a default value here but if you wanted to, you could assign a default value between the beginning <sql:param> element and the closing tag for this element.

                    

 

We include the <sql:query> element next which contains the stored procedure to be executed followed by the input parameters for this stored procedure. Then we have the closing tag for the root element:

 

XML Template Summary

This section has taken a look at XML templates. We have seen how we can code and execute SQL statements and stored procedures in templates. This helps to provide better security, as the code that retrieves the data is hidden from the end user.

 

We have also seen how using templates reduces the amount of data that needs to be entered in the URL of the browser.

 

There is an added benefit that has not yet been discussed. Like stored procedures, templates reside in one central place; this allows us to enhance the templates and have them immediately available to everyone who executes them. This helps to reduce our maintenance costs and the time spent updating our code, because we need only make the change in one place.