Using SQL Server 2000's HTTP Query Features You can use SQL Server 2000's HTTP (URL) query features independently of Access to return XML data documents to XML-enabled browsers. Template queries can combine an XML template document, which includes the SQL statement for the query and a custom XSL file to deliver a formatted HTML table. Note The following three sections provide only a brief description of HTTP and template queries. The "Accessing SQL Server Using HTTP" and "Executing SQL Statements Using HTTP" topics of SQL Server Books Online offers more detailed information on the types and syntax of HTTP queries that SQL Server 2000 offers.
Returning XML Documents from HTTP Queries HTTP queries append ?sql= and a T-SQL query to the URL for the \Inetpub\wwwroot\NorthwindCS SQL server virtual directory you established in the earlier "Setting Up the IIS Virtual Directory for the Database" section. As noted earlier, you can type or paste the complete URL into IE's Address text box for a quick test that returns raw XML data to a Web page. The http://oakleaf-xp1/NorthwindCS?sql=SELECT+*+FROM+%22Sales+Totals+by+Amount%22+for+xml+auto,elements&root=dataroot URL that the Top100Orders.htm page sends to SQL Server is a typical example of an HTTP query. Following are the elements of the HTTP query: ?sql= specifies that the following URL components are a T-SQL statement. + symbols substitute for spaces in the SQL statement. %22 identifiers surround table, view, function, or stored procedure names that contain spaces. for+xml+auto (the T-SQL FOR XML AUTO modifier) specifies that the data is to be returned as an XML data document. elements specifies element-centric XML, which is required by Access. If you don't add the elements modifier, the query returns attribute-centric XML. &root=dataroot generates a well-formed XML document by adding <dataroot>...</dataroot> tags to the XML data.
Note One application for ad hoc HTTP queries is generating XML data from selections users make in an HTML FORM. Assembling the selections into an HTML query, however, requires a substantial amount of script.
To test drive the preceding HTTP query, do this: Open the Top100Orders.htm file in Notepad, and turn Word Wrap off. Select the URL in the text starting with http:// and ending with dataroot and copy the URL to the Clipboard. (Don't include the trailing quote or the , false argument of the objDataXMLHTTP.open method.) Open IE, paste the URL to the Address box, and press Enter to display the element-centric XML data (see Figure 23.22). The XML data document forms the MSXML.DOMDocument, which you can manipulate with XSLT and VBScript or ECMAScript. Figure 23.22. This element-centric XML data illustrates use of the hexadecimal _0x0020_ literal that substitutes for spaces in element names. SQL server datetime values return in XSD dateTime text format.  Choose View, Source to open the unformatted stream of XML data in Notepad, select All Files in the Files of Type list, accept the default UTF-8 encoding, and save the file as Top100SQL.xml in your working folder, typically ...\Office11\Samples (not the ...\wwwroot\Reports folder).
You import the Top100SQL.xml file to an Access table in the later "Importing XML Data to Tables" section. Using SQL Server Template Queries Typing a lengthy T-SQL query in IE's Address box isn't a piece of cake, so Microsoft provides a template feature that lets you pass conventional T-SQL statements to the server. Using a template hides the SQL statement from the HTML source and prevents users from executing ad hoc queries. Tip You must clear the Allow URL Queries check box on the Settings page of the VirtualDirectoy Properties page for the SQL Server virtual directory to prevent users or Web pages from executing HTTP queries.
Templates are XML documents that you store in a subfolder of the SQL Server virtual directory for the target database you created in the earlier "Setting Up the IIS Virtual Directory for the Database" section. You added the Templates virtual name, but not the Templates subdirectory. Navigate in Explorer to \Inetpub\wwwroot\NorthwindCS and add a Templates subfolder to store the template files you create in the following sections. Writing and Executing a Simple XML Template File A template is a well-formed XML document with the standard XML header, a <dataroot>...</dataroot> tag pair that declares the SQL Server XML namespace (xml-sql), and an <sql:query>...</sql:query> tag pair to enclose the SQL statement, which you write in ordinary T-SQL. You don't add the &root=dataroot argument because the template adds <dataroot...> with an xmlns:sql namespace attribute value. XML The XML content of the template file for the Top100Orders query is [View full width] <?xml version='1.0' encoding='UTF-8'?> <dataroot xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:query> SELECT * FROM [Sales Totals by Amount] FOR XML AUTO,  ELEMENTS </sql:query> </dataroot>
|
Executing a template file requires a URL with the following format: http://servername/virtualdirectory/virtualname/template.xml To create the Top100Orders.xml template in the virtual name folder and execute the template query from IE, do this: Open Notepad, type the preceding XML text, and save the file as Top100Test.xml in your virtual name folder ...\wwwroot\NorthwindCS\Templates for this example. Note  | If you don't want to type the XML yourself, Top100Test.xml is included in the \Seua11\Chaptr23 folder of the accompanying CD-ROM. |
Launch IE and type http://servername/northwindcs/templates/top100test.xml as the URL. The template returns an XML data document that's identical to that for the HTTP query in the earlier "Returning XML Documents from HTTP Queries" section (refer to Figure 23.22).
You also can execute stored procedures and specify parameter values with HTTP template queries. The SQL Server Books Online topics mentioned earlier have syntax examples. Applying a Style Sheet to XML Data from Template Queries HTTP query templates can specify a style sheet, which unfortunately you must write from scratch. A minor addition to the template's XML code specifies the name of the associated XSL file. In this case, you process the default attribute-centric XML delivered by the FOR XML AUTO modifier. XML The XML template document Top100Test.xml for the Top100Orders query must be modified as shown below and saved as Top100.xml for use with a template style sheets named Top100.xsl: [View full width] <?xml version='1.0' encoding='UTF-8'?> <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql' sql:xsl='/Top100.xsl'> <sql:query> SELECT OrderID, CompanyName, CONVERT(nvarchar, ShippedDate, 101) AS ShippedDate, CONVERT(nvarchar, SaleAmount, 1) AS SaleAmount FROM [Sales Totals by Amount] FOR XML AUTO </sql:query> </ROOT>
The sql:xsl='/Top100.xsl' attribute specifies the name of the XSLT file that generates the HTML code. (The Top100.x?? file names prevent overwriting Top100Orders.x??.) Unlike Access XML style sheets, which use the Format function to format date and currency values, you must use T-SQL's CONVERT or CAST functions to deliver pre-formatted text to the stylesheet. The example template style sheet expects attribute-centric XML data, so the ELEMENTS modifier isn't present. |
The structure of the XSLT code for template style sheets is similar to that described in the earlier "Reformatting HTML Tables and Adding Page Elements" section, but considerably simpler. No script is required to process the XPath expressions. Code in the preceding style sheets generated by RPT2HTML4.xsl "walks the DOMDocument tree" sequentially. In this case, the XSLT processes the XML data as a single "chunk." XSL The following XSLT code in Top100.xsl generates a formatted table from the attribute-centric XML that the Top100.xml template generates [View full width] <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl' > <xsl:template match = '*'> <xsl:apply-templates /> </xsl:template> <xsl:template match = 'Sales_x0020_Totals_x0020_by_x0020_Amount'> <TR> <TD align="center"><xsl:value-of select = '@OrderID' /></TD> <TD><xsl:value-of select = '@CompanyName' /></ TD> <TD align="right"><xsl:value-of select = '@ShippedDate' /></TD> <TD align="right">$<xsl:value-of select = '@SaleAmount' /></TD> </TR> </xsl:template> <xsl:template match = '/'> <HTML> <HEAD> <STYLE> TH, TR, P { font-family: verdana, arial; font-size: 12px } </STYLE> <STYLE> TH { background-color: #CCCCCC } </ STYLE> <STYLE> TR { background-color: #EEEEEE } </ STYLE> </HEAD> <BODY> <P><B>SQL Server Query: </B> SELECT OrderID, CompanyName, CONVERT( nvarchar, ShippedDate, 101) AS ShippedDate, CONVERT(nvarchar, SaleAmount, 1) AS SaleAmount FROM [Sales Totals by Amount] FOR XML AUTO</P> <CENTER> <TABLE Border = '0'> <TR><TH colspan='4'>SQL Server 2000 HTTP Template Query</TH></TR> <TR> <TH>OrderID</TH> <TH>CompanyName</TH> <TH>ShippedDate</TH> <TH>SaleAmount</TH> </TR> <xsl:apply-templates select = 'ROOT' /> </TABLE> </CENTER> </BODY> </HTML> </xsl:template> </xsl:stylesheet>
The <xsl:value-of-select = '@ColumnName' /> expressions return the column values of the specified column to the four HTML <TD>...</TD> elements. The <xsl:apply-templates select = 'ROOT' /> expression merges the first <TR>...</TR> blocks in the code with the HTML code for the title and table header. |
Use the following URL to execute template/style sheet pairs: http://servername/virtualdirectory/virtualname/template.xml?contenttype=text/html. Omitting the ?contenttype=text/html argument returns the HTML code generated by the style sheet instead of a formatted page. | Top100.xml and Top100.xsl are included in the \Seau10\Chaptr23 folder of the accompanying CD-ROM. To give the sample files a test run, do the following: |
Copy the Top100.xml and Top100.xsl files from the CD-ROM or your \Program Files\Seua11\Chaptr23 folder to the Templates virtual name subfolder of your SQL Server virtual directory ...\Wwwroot\NorthwindCS\Templates for the examples of this chapter.  Launch IE and type http://servername/northwindcs/templates/top100.xml?contenttype=text/html in the Address box, and press Enter. The T-SQL statement of the template file and the formatted table appear as shown in Figure 23.23.
Figure 23.23. Formatting of the HTML table generated by the SQL Server HTTP template query and style sheet is similar to that of the tables you created earlier in the chapter.  Tip If you see strange characters when the page opens in IE, right-click the page and select Encoding, Unicode (UTF-8) to correct the problem.
|
