XML and the SQL 2000 Server, Part 2

In this second article in a series, the basics involved in XML to SQL 2000 Server over HTTP are explained.

Contributed by Jayaram Krishnaswamy
Rating:

/ 9
March 14, 2005

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Mapping Virtual Objects

In the configuration of the virtual directory, the virtual name to access database objects is dataObjects, as seen in this picture. Similarly the templates are stored in the virtual directory named Templates, and schema files in the folder Schema (local paths are shown in the first picture). It is using these virutal names that the database is accessed, as discussed later in the tutorial.

Queries Using for XML Clause

Again in Part 1, details of running queries in SQL 2000 Server's Query Analyzer were discussed. Most important to note were the facts that, depending on the clause used, both attribute-centric as well as element-centric representation of SQL Data formatted in XML can be returned. Since the data returned is only an XML fragment, it needs to be enclosed between root elements for it to become well formed. Furthermore, using the attribute XMLData for the for XML clause, an XML schema for the underlying data is returned together with the data.

Queries Over HTTP

Consumers of data from remote locations can access data using the HTTP protocol in several ways, including direct querying as well as Web services. Querying over HTTP can be carried out in a couple of different ways:

Encoded SQL in the URL of the Virtual Directory
Execute stored templates
Directly acessing the database objects

Using Select Queries in URL

In sending SQL commands to a URL, the command is sent in the form of a Query String named sql to the virtual root. For example, to send the following query (a + replacing a white space ):

Select * 
from authors
for xml auto

The string can be constructed as follows:
?sql=select+*+from+authors+for+xml+auto&root=root

Here,
?sql= represents a valid SQL statement to be executed by the SQL Server, Select+*+from+authors+xml+auto is the statement
and &root=root returns the XML as well formed XML by enclosing the SQL Server returned fragment inside <root></root>.

For example, by sending the following request to the default site XPHTEK and virutal root PubsVirtual:
http://xphtek/PubsVirtual/?sql=select+*+from+authors+for+xml+auto&root=root
The following result is displayed on the browser. Only part of the result is shown here.

Running this query in Netscape 7.2 adds an extra comment: "The XML file does not appear to have any style information attached with it."

Using Select Query with a Where Clause

In most business activities there is seldom a necessity to return all rows from the database. Filtering the required data and sending it to the client is the best way.Here is a filtered query and the result:
http://xphtek/PubsVirtual/?sql=select+*+from+authors+where+au_lname=
'Bond'+for+xml+auto&root=root

It is also possible to send the same query as follows to obtain the same result:
http://xphtek/PubsVirtual/?sql=select%20*%20from%20authors%20where%20au_lname='Bond'%20for%20xml%20auto&root=root

Stored procedure from a URL

It is also possible to access a stored procedure from a URL. Consider the following stored procedure on the SQL Server in th pubs database. Since a stored procedure needs to be executed, the object, namely MySimpleXML2, must be configured such that the user accessing the procedure has the permissions.

Create procedure MySimpleXML2 
@State char(2)
as
Select stor_name, city, zip, state
from stores
where state=@State
order by city 
for XML auto

The following query can be used to access this stored procedure and the result is shown in the next picture. http://xphtek/PubsVirtual/?sql=MySimpleXML2+'CA'&root=root

Considerations When Sending Queries to a URL

The string sent to the URL should be in one unbroken line and is also case sensitive. If the lines in this tutorial are wrapped into two or more lines, make sure that entering into the URL there is only one line.

Several TSQL symbols have a different meaning for the URLs. For example + in TSQL means arithmetic summing, but in URL it stands for a single white space. Similarly there are a number of characters that need to be replaced by what the URL can interpret. For example, if you need to find what character needs to be sent to the URL to represent an arithmetic sum, you could run server code, for example: <%Response.Write(Server.UrlEncode("+"))%>. For any other character, substitute the "+" in the above and run the code.

Here is an example of a query that needs to be properly encoded for the URL. It is an artificial example, but this can lead to an error if it is not encoded properly. Here 2+qty needs to be properly encoded for the URL. Note the XML queries can only return values into named columns only and therefore the name "Twice" for the column "2+qty".

select stor_id, qty, 2+qty as Twice 
from sales
for xml auto

The string sent to the URL is:

http://xphtek/PubsVirtual/?sql=select+stor_id+,+qty+,+(2%2Bqty)+as+Twice+from+sales+for+xml+auto&root=root

and the result of this query is:

In the URL access to data it should be appreciated that no file was involved, the query went directly to the server, albeit the mediation of the virtual directory. The ISAPI filter parsed the SQL sent and returned the result in HTML over HTTP. Herein lies the weakness of this method of access as well, since malicious code can be sent to the Server by editing the URL string.

Accessing the Database Objects

During configuration of the Virtual names it was seen that there is a support to access database objects such as tables and views via XML. This access makes it possible for users less comfortable with TSQL syntax to retrieve data from SQL server using XML. However, it will be necessary to understand the workings of XPath syntax to access such objects.

The next picture shows the authors table from the pubs database. Here au_id is the primary key for this table.

The syntax for the path to the first name, namely, au_fname is: [@au_id]/[@au_fname]. This path needs to be included in the dataObjects (which is the name of the virtual folder dbObject) to retrieve information. The string to be presented to the browser to find the au_fname for the author whose au_id is "111-34-3459" is:

http://xphtek/PubsVirtual/dataObjects/authors[@au_id='111-34-3459']/@au_fname

The result obtained by this is shown in the following picture, just a single element. Given the au_fname, the au_id can also be retrieved

This works fine as long as the result returned is from a single column only. An attempt made to retrieve more columns, or all columns using XPath, will result in an error. In order to return multiple results, the template query is the one that must be used.

What are template queries?

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:

Stored procedures with templates

The following stored procedure takes an input variable @State and returns a number of columns from the authors table in the pubs database:

Create procedure MySimpleXML2 
@State char(2)
as
Select stor_name, city, zip, state
from stores
where state=@State
order by city 
for XML auto

Now a template file (StoredProc.xml) to query the database with this stored procedure is fashioned as follows:

The result from this stored procedure is obtained by executing the procedure, which is accomplished by sending it to the URL as follows:

http://XPHTEK/PubsVirtual/Templates/StoredProc.xml?State=CA for authors from California
and
http://XPHTEK/PubsVirtual/Templates/StoredProc.xml?State=WA for authors from Washington.

The results are as shown here for both cases:

Display XML results with style sheets

In the generic format of the template files seen so far, the style sheet reference sql:xsl='xsl File Name' has not been used. If we reference a xsl style sheet, it will be possible to see the node tree. This can be displayed as an HTML document by further indicating that the content type is HTML.

Style sheet document structure

The specific case of displaying the results of the stored procedure, which shows the results of the Stored Procedure MySimpleXML2 considered earlier, will be formatted in HTML using a style sheet, CAtest.xsl. Using the generic template shown earlier, the template is modified to include the style sheet for formatting as follows:

Compare this with the one without reference to the style sheet information. The additional information needed is the xsl file, CATest.xsl. This style she/A>. This earlier output can be processed by the following xsl file.

This file should be located in the same directory as the StoreProcStyled.xml file, namely the Templates directory. If this file is accessed by its URL, namely,
http://xphtek/PubsVirtual/Templates/StoredProcStyled.xml?State=CA the output will be the node structure, as follows:

Display the HTML file

In order to display the HTML output, the ContentType=text/html should be appended to the URL string as follows:
http://xphtek/PubsVirtual/Templates/StoredProcStyled.xml?State=CA&ContentType=text/html. When this string is sent to the URL, the resulting HTML document will be displayed as shown in the next picture.

Summary

In this tutorial, several examples of URL access using query strings sent directly to the virtual directory root were shown. In fact, whether a virtual directory is properly configured or not can be ascertained by sending URL queries and examining the results. Also considered was the URL accessing of database object. Secure method of accessing a SQL server was shown using template files. Both SQL statements and stored procedure examples were given. HTML display of results from template queries with XSL style sheet and content type instructions was also described. In Part 3, the usage of XPATH queries to access data from SQL 2000 Server will be discussed.