Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
What Are Our Capabilities When Utilizing HTTP?
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
Placing a SQL query in a URL like this is simple enough, don't you think? After the 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."
FOR XML AUTO is a new extension to the SELECT statement making its appearance with SQL Server 2000. It will be covered in depth in Chapter 8, "OPENXML." For now, it's only necessary to know that it returns the results of the SQL statement as an XML document instead of as a standard rowset, as you're probably used to. You might also see it as FOR XML RAW or FOR XML EXPLICIT. Hang in there for now; we'll get to it. If you try leaving out the FOR XML statement with code similar to the following:
http://griffinj/Nwind/?sql=select+*+from+Employees&root=root
You'll see an error message in your browser that's very similar to what is shown in Listing 4.1.
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.