XML and SQL Server 2000
| Up to this point, we've given a lot of examples to show how the HTTP protocol is used to request XML documents from SQL Server. We've never really given a formal definition of the syntax. Table 4.3 gives the formal definition. We'll also cover some more details of querying via a URL, such as stored procedures, templates in depth, utilizing XSLT stylesheets, and so on. Here's the formal syntax for URL access accepted by the SQL ISAPI extension: http: //iisserver/virtualroot/virtualname[/pathinfo][/XPathExpression] [ ?param =value[ ¶m =value]...n] or: http://iisserver/virtualroot?{sql=SqlString template=XMLTemplate} [¶m=value[¶m=value]...n] Table 4.3. HTTP Syntax Explanation
Well-Formed Documents, Fragments, and &root
As promised , the &root=root parameter used in some of the URLs and template files in the previous section requires a little explanation. Think back to our discussion of well- formedness and XML documents. One of the conditions that must be met for an XML document to be well-formed is that it must contain a single top-level element. Remember our RESUMES shown in the following example: <RESUMES xmlns='http://www.myorg.net/tags'> <PERSON PERSONID="p1"> <NAME> ... </RESUMES> Our document contains the single top-level element <RESUMES> . Although there are other requirements for well-formed documents, if this one isn't met, the document fails the test. How does this relate to the &root parameter in the URL? The &root parameter specifies the name of the document ROOT element. The result is an XML document with that all-important single top-level element. Let's look at some examples by reusing some of the code we have given previously: http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO&root=root Here we specify &root=root . This will generate a document that contains that single top-level element, and the element will be <ROOT> . In the following example, you would expect a document fragment to be returned because no root element is specified. You actually receive an error message stating , "Only one top-level element is allowed in an XML document." Because the root element is missing, all employee elements are assumed to be at the top level, which isn't allowed. http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO When template files are used, the same conditions hold true. Let's say we have the template file shown in Listing 4.4. Listing 4.4 Template File Without a Root Element
<ROOT XMLNS:SQL="urn:schemas-microsoft-com:xml-sql"> <sql:query> SELECT * FROM Employees FOR XML AUTO </sql:query> </ROOT> Let's say we employ this template file via this URL: http://IISServer/Nwind/TemplateVirtualName/template.xml The template file will provide the single top-level element via the <ROOT> element declaration. Just to make sure we understand the root declaration, if I make the specification root=EMPS in the template file, the following fragment shows how the resulting document's root element has changed. <EMPS xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Employees EmployeeID="1: LastName="Davolio" ... ... </EMPS> Queries on Multiple Tables
Querying multiple tables has implications that you need to consider carefully if you want to generate your resulting documents with the proper element order. Here's the rule of thumb: "The order in which tables are specified in the SQL query determines the element nesting order." We'll take a look at the Orders and Employees tables in Northwind in a couple of ways (also refer to Appendix A, "Northwind Database Schema"). Here's the first query: http://iisserver/Nwind?sql=SELECT+TOP+2+Orders.OrderID,+Employees. LastName,+Orders.ShippedDate+FROM+Orders,+Employees+WHERE+Orders. EmployeeID=Employees.EmployeeID+Order+by+Employees.EmployeeID, OrderID+FOR+XML+AUTO& root=ROOT This returns the results in Listing 4.5. Listing 4.5 Results of Querying Multiple Tables
<?xml version="1.0" encoding="utf-8" ?> <ROOT> <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00"> <Employees LastName="Davolio" /> </Orders> <Orders OrderID="10270" ShippedDate="1996-08-02T00:00:00"> <Employees LastName="Davolio" /> </Orders> </ROOT> Let's do this again with three tables, this time adding the Order Details table. http://iisserver/Nwind?sql=SELECT+TOP+2+Orders.OrderID,+Employees. LastName,+Orders.ShippedDate,+[Order+Details].UnitPrice,+[Order+ Details].ProductID+FROM+Orders,+Employees,+[Order+Details]+WHERE+Orders. EmployeeID=Employees.EmployeeID+AND+Orders.OrderID=[Order+Details].OrderID +Order+by+Employees.EmployeeID,Orders.OrderID+FOR+XML+AUTO&root=ROOT Listing 4.6 shows the results of this query. Listing 4.6 Results of Querying Three Tables
<?xml version="1.0" encoding="utf-8" ?> <ROOT> <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00"> <Employees LastName="Davolio"> <Order_x0020_Details UnitPrice="15.2" ProductID="2" /> <Order_x0020_Details UnitPrice="17" ProductID="5" /> </Employees> </Orders> </ROOT> Listing 4.7 shows the results obtained when we execute the same SQL expression but move the Employees.LastName element to the last element specified. Listing 4.7 Results of Moving Employees.LastName to the Last Element
<?xml version="1.0" encoding="utf-8" ?> <ROOT> <Orders OrderID="10258" ShippedDate="1996-07-23T00:00:00"> <Order_x0020_Details UnitPrice="15.2" ProductID="2"> <Employees LastName="Davolio" /> </Order_x0020_Details> <Order_x0020_Details UnitPrice="17" ProductID="5"> <Employees LastName="Davolio" /> </Order_x0020_Details> </Orders> </ROOT> The key point I want you to grasp here is that the placement of elements in the result XML document depends on their placement in the SQL expression. This should be especially evident in the difference between Listings 4.6 and 4.7. Passing Parameters
It is possible to pass parameters to SQL queries in URLs. This is known as run-time substitution as opposed to design-time substitution. In this case, we use a placeholder to specify the location where the parameter is to be substituted at execution time. The placeholder is a ? , which must be specified as %3F in a URL. Here's an example: http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID+FROM+Orders+WHERE+ EmployeeID=%3F +FOR+XML+AUTO&EmployeeID=5&root=ROOT Listing 4.8 is the resulting document. Listing 4.8 Results of Parameter Substitution in Our URL
<?xml version="1.0" encoding="utf-8" ?> <ROOT> <Orders OrderID="10248" /> <Orders OrderID="10254" /> <Orders OrderID="10269' /> <Orders OrderID="10297" /> </ROOT> Passing multiple parameters would just consist of more than one item with a question mark and a separate parameter for each. This URL passes two parameters: http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID+FROM+Orders+WHERE+ EmployeeID=%3F+AND+CustomerID=%3F+FOR+XML+AUTO&EmployeeID=5&CustomerID= "VINET"&root=ROOT This might not seem like a big deal here, and perhaps it's not to you, but wait until we start specifying template files in URLs that have parameters queries designed into them. We'll get to these shortly. The XSL Keyword
Now we get to make use of what we learned in Chapter 2. Utilizing XSLT stylesheets gives us the much-needed flexibility to manipulate the XML output we generate. We can create HTML on-the-fly for immediate or later display, or we can change the returned XML document to a different one for further processing. The latter occurs more often than you would think, as with Electronic Data Interchange (EDI) related messages. Let's take the following SQL query and generate an HTML page: http://iisserver/Nwind?sql=SELECT+TOP+4+OrderID,EmployeeID,Shipname+FROM+ Orders+WHERE+EmployeeID=5+FOR+XML+AUTO&xsl=order.xsl &root=ROOT In this example, the XSL file is located in the virtual root directory.The resulting XML document is given in Listing 4.9, followed by the XSLT stylesheet in Listing 4.10. Listing 4.9 XML Document Containing Order Information
<?xml version="1.0" encoding="utf-8" ?> <ROOT> <Orders OrderID="10248" EmployeeID="5" Shipname="Vins et alcools Chevalier" /> <Orders OrderID="10254" EmployeeID="5" Shipname="Chop-suey Chinese" /> <Orders OrderID="10269" EmployeeID="5" Shipname="White Clover Markets" /> <Orders OrderID="10297" EmployeeID="5" Shipname="Blondel pre et fils" /> </ROOT> Listing 4.10 XSLT Stylesheet to Apply to Order Information
<?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/XSL/Transform/1.0"> <xsl:output media-type="text/html"/> <xsl:template match="/"> <HTML> <BODY> <TABLE width="400" border="1"> <TR> <TD><B>Order ID</B></TD> <TD><B>Ship Name</B></TD> </TR> <xsl:apply-templates/> </TABLE> </BODY> </HTML> </xsl:template> <xsl:template match="Orders"> <TR> <TD> <xsl:value-of select="@OrderID"/> </TD> <TD> <xsl:value-of select="@Shipname"/> </TD> </TR> </xsl:template> </xsl:stylesheet> This results in a simple table of Order ID versus Shipname, as shown in Table 4.4. Table 4.4. HTML Table of Results
Again, the result in this case might be simple, but the potential is enormous . Data can be retrieved from a database and presented in real-time to the viewer via a thin-client browser. Static pages can be generated and stored for viewing as needed by the client. In this case, the XSLT stylesheet functions as an HTML template. (Templates are much easier to maintain than rewriting HTML documents.) Business-to-business e-commerce documents can be generated from existing queries without having to modify the query itself. The XSLT stylesheet can manipulate the data in any way desired to create the required new XML document. Next we change our focus to using template files to generate XML documents. We'll discuss them in a lot more detail than we have so far. |