We can also specify the root element for our XML data, thus
eliminating the need to specify the Root keyword in the URL.
A D V E R T I S E M E N T
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>
