XML and the SQL 2000 Server, Part 2 - What are template queries?
(Page 5 of 7 )
First of all, a template is a well formed XML document. The document contains one or more SQL or Xpath queries; SQL statements such as select, insert, delete, and update; stored procedures; xpath queries; and xsl. The vulnerabilities of URL queries are avoided because the SQL is hidden in a file kept in the directory of type template.
The generic format of a template syntax which has the extension xml is shown here:
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl='xslFilename'>
<sql:header>
<sql:param> ... </sql:param:>
<sql:param> ... </sql:param:>
<sql:param> ... </sql:param:>
.
.
</sql:header>
<sql:query>
SQL Statements
</sql:query>
<sql:xpath-query mapping-schema="SchemaFileName.xml">
XPath Query
</sql:xpath>
</root>
It was seen earlier that the for xml clause returns a XML Fragment rather than a well-formed XML document. The first line esablishes a <root> top level element for the XML document besides declaring the namespace. The sql:xsl='xslFilename' refers to an applicable style sheet for formatting the document. The path for this file can be relative, or absolute.
The <sql:header> tag is optional and when used with queries requiring parameters, single parameter per line of <sql:param> ... </sql:param:> are inserted here. Multiple parameters may be specified. Rules of well formed document requires correct closing tags.
The <sql:query> tag is where the SQL statements are inserted. It is permissible to place multiple SQL statements here. Each statement is considered as a different transaction. Stored procedures may also be placed here.
<sql:xpath-query mapping-schema="SchemaFileName.xml"> specifies the XPath query. The XPath query is always executed against the annotated XDR schema. This attribute must always be present for the XPath query. This will be considered in a future article. For now the focus is on queries.
Once the template file is created, it should be placed in, or uploaded to, the Template directory of the server's local file system, so that it can be processed by the server. A couple of examples will be considered in the following section to make the usage of template files clearer.
Accessing data with a template file Although a generic syntax was described, it is not necessary to have all the elements to run a template query. A simple example of a template file is:
Now this file (named NCity.xml) is placed in the Template directory as mentioned earlier. The template file can be accessed by sending the following string to the URL:
http://xphtek/PubsVirtual/Templates/NCity.xml to yield the following result:
Next: Stored procedures with templates >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
