Using XML Templates (MSSQL manual)

sql|xml| templates

The following is a generic form of a template that shows how to specify SQL queries and XPath queries:

<root xmlns:sql= "Urn:schemas-microsoft-com:xml-sql"
Sql:xsl= ' xsl FileName ' >
<sql:header>
<sql:param> </sql:param>
<sql:param> </sql:param>...n
</sql:header>
<sql:query>
SQL statement (s)
</sql:query>
<sql:xpath-query mapping-schema= "Schemafilename.xml" >
XPath Query
</sql:xpath-query>
</ROOT>

Any element in the template is optional. The elements

<ROOT>

Specify this tag to provide a single top-level element (also known as the root tag) of the resulting XML document. The <ROOT> tag can be any name.

<sql:header>

This tag is used to hold all header values. In the current implementation, the <sql:param> element can only be specified in this tag. <sql:header> is used as the inclusion tag, allowing you to define multiple parameters. All parameter definitions are in one place, which makes processing parameter definitions more efficient.

<sql:param>

This element is used to define parameters that are passed to the query within the template. Each <param> element defines an argument. You can define multiple <param> elements in the <sql:header> tag.

<sql:query>

This element is used to specify the SQL query. You can specify multiple <sql:query> elements in the template.

<sql:xpath-query>

This element is used to specify an XPath query. Because an XPath query executes on an annotated XML data simplification (XDR) schema, you must specify the schema file name using the Mapping-schema attribute.

Sql:xsl

This attribute is used to specify an Extensible Stylesheet Language (XSL) style sheet that will be applied to the resulting XML document. When you specify a mapping schema file, you can specify a relative or absolute path. The specified relative path is relative to the directory associated with the virtual name of the template type. For example, if the directory associated with the virtual name of the template type is C:\Template, the relative path specified for sql:xsl will be mapped to C:\Template\Xyz\MyXSL.xml.

Mapping-schema

This attribute is used to identify an XDR schema with annotations. This attribute is specified only when an XPath query is executed in the template. XPath queries are executed on annotated XDR schemas. When you specify a mapping schema file, you can specify a relative or absolute path. The specified relative path is relative to the directory associated with the virtual name of the template type. For example, if the directory associated with the virtual name of the template type is C:\Template, the relative path (Schema/mschema.xml specified for Mapping-schema) is mapped to the C:\Template\Schema\ Mschema.xml.

Explains that each <sql:query> or <sql:XPath-query> represents a separate transaction. Therefore, if you have more than one <sql:query> or <sql:XPath-query> tag in the template, the other tag will continue when one of the tags fails.

If ContentType is set, Sqlisapi.dll returns the header information to the browser. If ContentType is not set, Urlmon uses the first character in the template file to determine the content type. If the first character in the template is a < character or a Unicode byte order flag (0xFFFE), the Text/xml is returned to the browser as the content type, and the browser displays the results. Otherwise, Sqlisapi.dll does not send content-type header information that instructs the browser how to display the results, so the results will not be visible in the browser.

Before you can specify a template in a URL template, you must create a virtual name of the template type using the IIS virtual Directory Management utility for SQL Server. For more information, see Using the IIS Virtual Directory Management utility for SQL Server.

Storage templates
The template is stored in the directory associated with the virtual name of the template type or in one of its subdirectories:

If the template is stored in a directory associated with a virtual name of type template, the form of a URL query is as follows:
Http://IISServer/nwind/TemplateVirtualName/TemplateFile.xml

If the template is stored in a subdirectory associated with the virtual name of type template (xyz), the form of the URL query is as follows:
Http://IISServer/nwind/TemplateVirtualName/xyz/TemplateFile.xml

Namespaces are not supported for XPath queries that are specified directly in the URL. If you want to use namespaces in an XPath query, you should use the template