Getting XML Data Through HTTP

So we have seen the syntax of the FOR XML clauses and its variations. But there is more to retrieving XML data from SQL Server than just typing in a query from a URL. Let’s look at some other methods of getting XML back to the client.

First, we will look at template files as a means of retrieving data. A template is a predefined file that resides in your virtual directory on a Web site. Since most queries tend to get complex, and normally we need a better format back from SQL instead of raw XML data, we can define templates that further enhance our XML experience.

With templates, we can apply XSL formatting to our XML document, and we can define long queries, pass parameters easily to queries, and declare XML namespaces. Another great reason to employ templates is security. When we start putting schema information in URLs, we are opening ourselves to allowing users to see data we might not really want them to see, such as the specifics of our queries. And if we consider removing URL query processing from the virtual root, we are letting the XML SQL Server XML ISAPI process the files and return the XML document, further improving security.

So let’s build a couple templates and see how they work out for us. For starters, we need to specify the template name in an IIS virtual root. We set up these virtual directories when we configure IIS.

Now, open up XML Notepad and type in the following:

 <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">   <sql:query>     SELECT top 2 ContactName, CompanyName     FROM Customers Where ContactName Like 'J%'     FOR XML AUTO   </sql:query> </ROOT>

This is a template in its most simple form. You can isolate the T-SQL very easily We are asking for the ContactName and CompanyName for Customers whose names begin with “J.” Save this document as xdesk1.xml in the C:\Inetpub\your_web_app directory. Now from the browser, type the following into the URL: http://localhost/your_web_app/template/ xdesk1.xml

The result should look like this:

 - <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">   <Customers ContactName="Janine Labrune" CompanyName="Du monde entier"/>   <Customers ContactName="José Pedro Freyre"    CompanyName="Godos Cocina Típica" />   </ROOT> 

How cool is that? We define a query, save it as a file, and just reference the virtual template directory and the template name from the browser-we are off to the races. From this small sample, the power of the template should become clearer to you.

Now we need to get a little more complex. Let’s run through a few scenarios that we might run into in real life. Most likely, our T-SQL resides in stored procedures on the server, and we normally do not do a “Select *”; we pass parameters to limit our result set to get useful data. So how can we do that? You got it: templates.

But let’s hold on for a second and examine the syntax of a template file. Understanding the syntax of the template will ensure success as we move forward.

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

Here are the specifics:

 <ROOT>

Required for a well-formed XML document. Since the template file follows the rules of a well-formed document, this is required.

 <sql:header>

This tag is used to hold any header values. In the current implementation, only the <sql:param> element can be specified in this tag. The <sql:header> tag acts as a containing tag, allowing you to define multiple parameters. With all the parameter definitions in one place, processing the parameter definitions is more efficient.

 <sql:param>

This element is used to define a parameter that is passed to the query inside the template. Each <param> element defines one parameter. Multiple <param> elements can be specified in the <sql:header> tag.

 <sql:query>

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

 <sql:xpath-query>

This element is used to specify an XPath query. Because the XPath query is executed against the annotated XML-Data Reduced (XDR) schema, the schema filename must be specified using the mapping-schema attribute.

 sql:xsl

This attribute is used to specify an XSL style sheet that will be applied to the resulting XML document. A relative or absolute path can be specified for the location of the style sheet, similar to the way you would reference a hyperlink or image in a Web page.

 mapping-schema

This attribute is used to identify the annotated XDR schema. This attribute is specified only if you are executing an XPath query in the template. The XPath query is executed against the annotated XDR schema. Just as when referencing a style sheet, a relative or absolute path can be specified here.