XML and SQL Server 2000
Now let's take a quick look at what we can accomplish by using the HTTP protocol. The rest of this chapter will then go into each of these functions in depth. Placing a SQL Query Directly in a URL
Take a look at the following: http://IISServer/Nwind?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO&root=root URL, which points to the Nwind virtual directory, insert a question mark followed by sql= and then the SQL query itself. Separate all words in the query with a plus (+) sign. We'll explain the &root parameter in the upcoming section "Well- Formed Documents, Fragments, and &root . "
Listing 4.1 Error Generated by Missing FOR XML Statement
<?xml version="1.0" encoding="utf-8" ?> <root> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="Streaming not supported over multiple column result"?> </root> Specifying a Template Directly in a URL
Here's an example of specifying a template directly: http://IISServer/Nwind?template=<ROOT+xmlns:sql="urn:schemas- microsoft-com:xml-sql"><sql:query>SELECT+*+FROM+Employees+FOR+XML+AUTO </sql:query></ROOT> Now you see an example of a template file. It is in the form of an XML document and contains one or more SQL statements. Templates allow the data to be returned as a well-formed XML document. As you'll see shortly, this isn't necessarily so when specifying a SQL statement directly in a URL. Also, some SQL query statements can become quite long. If they were in a template file, they would be easier to read than in a URL with all the additional markup needed (the plus [+] signs). Declaring a Template File in a URL
Rather than writing a very long URL statement similar to the one in the preceding section, we could put the SQL query in a template file and refer to it in the URL like this: http://IISServer/Nwind/TemplateVirtualName/template.xml Remember that the TemplateVirtualName was specified with the Virtual Directory Management utility. This also provides for better security by keeping the user away from the details of the database. Specifying an XPath Query Against a Schema File in a URL
The following example shows how this would look: http://IISServer/Nwind/SchemaVirtualName.schemafile.xml/ Employee[@EmployeeID=6] Here the SchemaVirtualName was specified with the Virtual Directory Management Utility, and Employee[@EmployeeID=6] is the XPath query executed against schemafile.xml. Specifying Database Objects Directly in a URL
Database objects such as tables and views can be specified in a URL, and then an XPath query can be issued against it to produce results as shown in the following example: http://IISserver/Nwind/dbobjectVirtualName/XpathQuery The XPath query is placed as the last entity in the URL, directly after the VirtualDirectoryName . |