Use XML templates

Use XML templates
The following is a general template that displays the specified SQL query and XPath query methods:

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

<Root>

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

<SQL: Header>

This tag is used to save all title values. In the current implementation scheme, you can only specify the <SQL: param> element in this tag. <SQL: Header> used as a include tag, allowing you to define multiple parameters. All parameter definitions are in one location, which makes processing parameter definitions more effective.

<SQL: param>

This element is used to define the parameters passed to the query in the template. Each <param> element defines a parameter. You can define multiple <param> elements in the <SQL: Header> tag.

<SQL: Query>

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

<SQL: XPath-query>

This element is used to specify the XPath query. Because XPath queries are executed in the simplified XML data (XDR) architecture with annotations, you must use the mapping-schema feature to specify the schema file name.

SQL: XSL

This feature is used to specify the Extensible style sheet language (XSL) style sheet that will be applied to the obtained XML document. When specifying a ing schema file, you can specify a relative or absolute path. The specified relative path is 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 XYZ/myxsl specified by SQL: XSL is used. XML maps to C:/template/XYZ/myxsl. XML.

Mapping-schema

This feature is used to identify the annotated XDR architecture. This feature is specified only when an XPATH query is executed in the template. The XPath query is executed in the annotated XDR architecture. When specifying a ing schema file, you can specify a relative or absolute path. The specified relative path is 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 is (schema/mschema specified by mapping-schema. XML) ing to C:/template/Schema/mschema. XML.

 
Each <SQL: Query> or <SQL: XPath-query> indicates a separate transaction. Therefore, if the template contains multiple <SQL: Query> or <SQL: XPath-query> tags, when one tag fails, other tags will continue.

If contenttype is set, sqlisapi. dll returns the title 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 Unicode byte sequence sign (0 xfffe), text/XML is returned to the browser as the content type and the browser displays the result. Otherwise, sqlisapi. dll does not send the content type title information that guides the browser to display the result. Therefore, the result is not displayed in the browser.

Before specifying a template in a URL template, you must use the IIS virtual directory management utility for SQL Server to create a virtual name of the template type. For more information, see use the IIS virtual directory management utility for SQL Server.

Storage Template
The template is stored in the directory associated with the virtual name of the template type or a sub-directory of the template type:

If the template is stored in the directory associated with the virtual name of the template type, the URL Query format is as follows:
Http: // iisserver/nwind/templatevirtualname/templatefile. xml

If the template is stored in a subdirectory associated with the virtual name of the template type (XYZ), the URL Query format is as follows:
Http: // iisserver/nwind/templatevirtualname/XYZ/templatefile. xml specify the XSL style sheet in the template to apply the Extensible style sheet language (XSL) style sheet to the query results. When you use HTTP to execute a template, you can use the following method to specify the XSL file: use SQL: XSL in the template. Use the XSL keyword as part of the URL to specify the XSL file that will be used to process the obtained XML data. If SQL: XSL is used in the template to specify the XSL file and the keyword XSL is used in the URL to specify the XSL file, the result is first applied to the XSL style table specified in the template, then apply the XSL file specified in the URL. In the following example, nwind is a virtual directory created using the IIS virtual directory management utility for SQL Server, template is the virtual name of the template type defined when a virtual directory is created (you can specify any virtual name for it when creating a virtual directory ). For more information, see use the IIS virtual directory management utility for SQL Server. A. Specify SQL: XSL in the template to process the results. In the following example, the template contains a simple SELECT statement. Follow the instructions in the XSL file specified by SQL: XSL to process the query results. <? XML version = '1. 0' encoding = 'utf-8'?>
<Root xmlns: SQL = 'urn: Schemas-Microsoft-com: XML-SQL'
SQL: XSL = 'myxsl. XSL '>
<SQL: Query>
Select firstname, lastname from employees for XML auto
</SQL: Query>
</Root>
For example, the template (templatewithxsl. XML) is stored in the directory associated with the template type virtual name (Template. The XSL file (myxsl. XSL) is also stored in the same directory. The following is an XSL file: <? XML version = '1. 0' encoding = 'utf-8'?>
<XSL: stylesheet xmlns: XSL = "http://www.w3.org/1999/XSL/Transform" version = "1.0">
<XSL: template match = 'ployees'>
<Tr>
<TD> <XSL: value-of select = '@ firstname'/> </TD>
<TD> <B> <XSL: value-of select = '@ lastname'/> </B> </TD>
</Tr>
</XSL: Template>
<XSL: template match = '/'>
<HTML>
<Head>
<Style> Th {background-color: # cccccc} </style>
</Head>
<Body>
<Table border = '1' style = 'width: 300; '>
<Tr> <TH colspan = '2'> employees </Th> </tr>
<Tr> <TH> first name </Th> <TH> last name </Th> </tr>
<XSL: Apply-templates select = 'root'/>
</Table>
</Body>
</Html>
</XSL: Template>
</XSL: stylesheet>
The following url execution template: http: // iisserver/nwind/template/templatewithxsl. xml? The contenttype = text/html result is displayed in the format of firstname and lastname. You can also specify an XSL file in the URL, instead of specifying the file in the template (using SQL: XSL. In this case, the XSL file must be stored in the directory associated with the virtual root (nwind) or its sub-directory, and the relative path must be specified in the URL. If the XSL file is stored in the directory associated with the nwind virtual directory, the following URL execution template is: http: // iisserver/nwind/template/templatefile. xml? XSL = myxsl. XSL
If the XSL file is stored in a sub-directory (x) in the virtual root directory, the following URL specifies the relative path: http: // iisserver/nwind/template/templatefile. xml? XSL =/X/myxsl. XSL
If SQL: XSL is used in the template and the keyword XSL is used in the URL to specify the XSL file, the XSL style sheet specified in the template is first applied to the result, then apply the XSL file specified in the URL.

Example XPath Query
In this example, the XPath query references the following two ing architectures. The ing architecture is a simplified XML-data (XDR) architecture with annotations. For more information about the ing architecture, see create an XML view using an annotated XDR architecture.

 

Note: You must create a virtual root to access the northwind database, template, and schema type virtual names before you can use the URL to execute the example XPath query. For information on creating the nwind virtual directory and virtual name, see create nwind virtual directory. For more information about using HTTP to access Microsoft SQL Server, see using HTTP to access SQL Server.

There are two ways to perform XPath queries on the annotated XDR architecture:

Create a template that contains an XPATH query. Then, execute the template in the URL (such as http: // iisserver/virtualroot/templatevirtualname/templatefile. XML. In this template, specify the ing architecture for which the XPath query will be executed. In this case, the ing architecture must be stored in the directory associated with the virtual name of the template type (or a sub-directory of it, specify a relative path as the value of the mapping-schema feature in the template.

You can specify the XPath query directly in the URL (for example, http: // iisserver/virtualroot/schemavirtualname/schemafile. xml/xpathquery. In this case, the schema file must be stored in the directory associated with the schema-type virtual name.
Example of annotated XDR Architecture
In all examples in this section, the XPath query is specified in the template for instructions, and the template is executed using HTTP. Therefore, you must use this ing schema file (sampleschema1.xml), which is saved in the directory associated with the template-type virtual Name:

<? XML version = "1.0"?>
<Schema xmlns = "urn: Schemas-Microsoft-com: XML-Data"
Xmlns: dt = "urn: Schemas-Microsoft-com: datatypes"
Xmlns: SQL = "urn: Schemas-Microsoft-com: XML-SQL">

<Elementtype name = "customer" SQL: Relation = "customers">
<Attributetype name = "customerid" DT: TYPE = "ID"/>
<Attributetype name = "companyName"/>
<Attributetype name = "contactname"/>
<Attributetype name = "city"/>
<Attributetype name = "fax"/>
<Attributetype name = "orders" DT: TYPE = "idrefs" SQL: ID-Prefix = "ord-"/>

<Attribute type = "customerid"/>
<Attribute type = "companyName"/>
<Attribute type = "contactname"/>
<Attribute type = "city"/>
<Attribute type = "fax"/>
<Attribute type = "orders" SQL: Relation = "orders" SQL: field = "orderid">
<SQL: Relationship
Key-relation = "customers"
Key = "customerid"
Foreign-relation = "orders"
Foreign-Key = "customerid"/>
</Attribute>

<Element type = "order">
<SQL: Relationship
Key-relation = "customers"
Key = "customerid"
Foreign-relation = "orders"
Foreign-Key = "customerid"/>
</Element>
</Elementtype>

<Elementtype name = "order" SQL: Relation = "orders">
<Attributetype name = "orderid" DT: TYPE = "ID" SQL: ID-Prefix = "ord-"/>
<Attributetype name = "employeeid"/>
<Attributetype name = "orderdate"/>
<Attributetype name = "requireddate"/>
<Attributetype name = "shippeddate"/>

<Attribute type = "orderid"/>
<Attribute type = "employeeid"/>
<Attribute type = "orderdate"/>
<Attribute type = "requireddate"/>
<Attribute type = "shippeddate"/>

<Element type = "orderdetail">
<SQL: Relationship
Key-relation = "orders"
Key = "orderid"
Foreign-relation = "[Order Details]"
Foreign-Key = "orderid"/>
</Element>
</Elementtype>

<Elementtype name = "orderdetail" SQL: Relation = "[Order Details]"
SQL: Key-fields = "orderid productid">
<Attributetype name = "productid" DT: TYPE = "idref"
SQL: ID-Prefix = "prod-"/>
<Attributetype name = "unitprice"/>
<Attributetype name = "quantity"/>

<Attribute type = "productid"/>
<Attribute type = "unitprice" SQL: field = "unitprice"/>
<Attribute type = "quantity"/>

<Element type = "discount" SQL: field = "discount"/>
</Elementtype>

<Elementtype name = "discount" DT: TYPE = "string"
SQL: Relation = "[Order Details]"/>

<Elementtype name = "employee" SQL: Relation = "employees">
<Attributetype name = "employeeid"/>
<Attributetype name = "lastname"/>
<Attributetype name = "firstname"/>
<Attributetype name = "title"/>

<Attribute type = "employeeid"/>
<Attribute type = "lastname"/>
<Attribute type = "firstname"/>
<Attribute type = "title"/>
</Elementtype>
</Schema>

Description

Group the sample query group by the XPath operation type executed by the query.