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:

<sql:header>
<sql:param name="Last_Name_VC">Willis</sql:param>
<sql:param name="Location_ID">1</sql:param> 
</sql:header>

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.

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

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:

http://localhost/htData/Template/EmployeeLocation.xml?
Last_Name_VC='Carson'+,+Location_ID=1&ContentType=Text/HTML

Try It Out - XML Template with Parameters

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:

<?xml version="1.0" encoding="UTF-8"?>
<Hardware xmlns:sql="urn:schemas-microsoft-com:xml-sql"
  sql:xsl="../SystemSpecs.xsl">
  <sql:header>
   <sql:param name="Hardware_ID"></sql:param> 
  </sql:header>
  <sql:query>
   EXECUTE up_parmsel_xml_hardware @Hardware_ID
  </sql:query>
</Hardware>

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.

http://localhost/htData/Template/
SystemSpecs.xml?Hardware_ID=1&ContentType=Text/HTML

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:

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

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.

           
  <sql:header>
   <sql:param name="Hardware_ID"></sql:param>
  </sql:header>     

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:

  <sql:query>
   EXECUTE up_parmsel_xml_hardware @Hardware_ID
  </sql:query>
</Hardware>

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.