XML and the SQL 2000 Server, Part 2 - Considerations When Sending Queries to a URL

(Page 4 of 7 )

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.

Next: What are template queries? >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: XML and the SQL 2000 Server-Part 2

>>> Be the FIRST to comment on this article!