Retrieving SQL Server Data in XML Format
SQL Server 2000 can produce results directly in XML format. This option makes data management from the client side easier, especially with Active Server Pages.
When SQL Server 2000 needs to produce results in XML format, there is an internal component that translates the internal rowset into an XML stream. The component to use depends on the selected XML mode.
The XML stream is sent to the client application and the data is shown using any of the available formatting capabilities in either the server or the client computer.
Query Analyzer cannot interpret XML data correctly, because it does not use the XML Parser at all. It shows XML streams as several text rows, each one up to 2033 bytes long. The reason for these Transact-SQL extensions is to access SQL Server data in XML format from HTTP connections or from Web applications, and not from Query Analyzer.
Note
Later in this chapter, the section "HTTP Access to SQL Server" teaches you how to configure Internet Information Server to provide HTTP access to SQL Server. You can follow those instructions to provide HTTP access to SQL Server, and then you can execute the exercises of this section using Internet Explorer to retrieve the XML results directly.
Using the FOR XML Clause
To retrieve data in XML format, you must use the new FOR XML extension for the SELECT statement. Listing 16.8 shows a simple SELECT statement that retrieves the data in XML format and the output produced as seen in Query Analyzer:
Listing 16.8—Using FOR XML to Produce Results in XML Format
USE Northwind GO SELECT CategoryID, CategoryName, Description FROM Categories FOR XML AUTO
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------------- <Categories CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales"/> <Categories CategoryID="2" CategoryName="Condiments" Description="Sweet and savory sauces, relishes, spreads, and seasonings"/> <Categories CategoryID="3" CategoryName="Confections" Description="Desserts, candies, and sweet breads"/> <Categories CategoryID="4" CategoryName="Dairy Products" Description="Cheeses"/><Categories CategoryID="5" CategoryName="Grains/Cereals" Description="Breads, crackers, pasta, and cereal"/> <Categories CategoryID="6" CategoryName="Meat/Poultry" Description="Prepared meats"/><Categories CategoryID="7" CategoryName="Produce" Description="Dried fruit and bean curd"/> <Categories CategoryID="8" CategoryName="Seafood" Description="Seaweed and fish"/> (8 row(s) affected)
CAUTION
Adjust the Maximum characters per column setting in Query Analyzer to a value larger than 2033, or some of the examples in this section will be truncated.
You can adjust this setting by going to the Tools, Options menu and entering the Results tab.
You can see in Listing 16.8 and its output some interesting things:
The only extra keywords sent to SQL Server were the FOR XML AUTO at the end of a standard SELECT statement.
The output in Query Analyzer shows only one column, with a bizarre name: XML plus a GUID-like expression.
The entire output goes to a single line, but the row count still says 8 row(s) affected. The output line is extremely long and it includes the entire result set.
Copy the line from the result set pane in Query Analyzer to Notepad, and add some carriage returns and tabs in the right places to make the result more XML-like, as shown in Listing 16.9.
Listing 16.9—Manually Formatted Query Output Using the FOR XML Clause
<Categories CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales"/> <Categories CategoryID="2" CategoryName="Condiments" Description="Sweet and savory sauces, relishes, spreads, and seasonings"/> <Categories CategoryID="3" CategoryName="Confections" Description="Desserts, candies, and sweet breads"/> <Categories CategoryID="4" CategoryName="Dairy Products" Description="Cheeses"/> <Categories CategoryID="5" CategoryName="Grains/Cereals" Description="Breads, crackers, pasta, and cereal"/> <Categories CategoryID="6" CategoryName="Meat/Poultry" Description="Prepared meats"/> <Categories CategoryID="7" CategoryName="Produce" Description="Dried fruit and bean curd"/> <Categories CategoryID="8" CategoryName="Seafood" Description="Seaweed and fish"/>
Save this document into a file called xmlListing9.xml and try to open it from Internet Explorer. You will receive the error message shown in Figure 16.7, because this XML output is not a well-formed XML document, as it contains more than one root element. This kind of document is called a XML document fragment.
){kind=link}
Figure 16.7 The direct output using the FOR XML clause is not a well-formed XML document.
Edit the document again and add a new root node, as shown in Listing 16.10. You can successfully open this document from Internet Explorer.
Listing 16.10—Adding a Root Node Converts the XML Output into a Well-Formed XML Document
<CategoryList> <Categories CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales"/> <Categories CategoryID="2" CategoryName="Condiments" Description="Sweet and savory sauces, relishes, spreads, and seasonings"/> <Categories CategoryID="3" CategoryName="Confections" Description="Desserts, candies, and sweet breads"/> <Categories CategoryID="4" CategoryName="Dairy Products" Description="Cheeses"/> <Categories CategoryID="5" CategoryName="Grains/Cereals" Description="Breads, crackers, pasta, and cereal"/> <Categories CategoryID="6" CategoryName="Meat/Poultry" Description="Prepared meats"/> <Categories CategoryID="7" CategoryName="Produce" Description="Dried fruit and bean curd"/> <Categories CategoryID="8" CategoryName="Seafood" Description="Seaweed and fish"/> </CategoryList>
However, you can use aliases to produce a better output that improves the element naming, as shown in the query from Listing 16.11. A more readable naming convention will call every element using the table singular name.
As an example, you can use the alias "Category" for the Categories table, providing in this way the name "Category" to each element in the XML output. Listing 16.12 shows the output after adding the root node Categories to complete the XML fragment produced that this query produces.
Listing 16.11—Using Table and Column Aliases to Improve XML Element Naming
USE Northwind GO SELECT CategoryID as ID, CategoryName as Name, Description FROM Categories as Category FOR XML AUTO
Listing 16.12—Output From Listing 16.11 After Adding the Categories Root Node
<Categories> <Category ID="1" Name="Beverages" Description="Soft drinks, coffees, teas, beers, and ales"/> <Category ID="2" Name="Condiments" Description="Sweet and savory sauces, relishes, spreads, and seasonings"/> <Category ID="3" Name="Confections" Description="Desserts, candies, and sweet breads"/> <Category ID="4" Name="Dairy Products" Description="Cheeses"/> <Category ID="5" Name="Grains/Cereals" Description="Breads, crackers, pasta, and cereal"/> <Category ID="6" Name="Meat/Poultry" Description="Prepared meats"/> <Category ID="7" Name="Produce" Description="Dried fruit and bean curd"/> <Category ID="8" Name="Seafood" Description="Seaweed and fish"/> </Categories>
In the preceding examples you selected the XML output in AUTO mode. SQL Server accepts the following modes:
RAW: Produces an XML stream as independent row elements, where every element is called row, regardless of the table name
AUTO: Produces an XML stream as independent row elements, where every element is named according to the table from where the data is read
EXPLICIT: Produces a Universal Table that can be used to have a tighter control on the way XML data is organized
Note
EXPLICIT mode exceeds the objectives of this chapter. If you are interested on detailed information on this XML mode, you can search for information and examples in Books Online, or attend the Microsoft Official Curriculum Course 2091A, "Building XML-Enabled Applications Using Microsoft SQL Server 2000."
Listing 16.13 shows the same query as in Listing 16.11, but in RAW mode, with its unformatted output. As you can see in the output, the main difference is the name of the elements, which in this case is always row.
Listing 16.13—Using FOR XML RAW to Produce Results in XML Format
USE Northwind GO SELECT CategoryID as ID, CategoryName as Name, Description FROM Categories as Category FOR XML RAW
XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------------- <row ID="1" Name="Beverages" Description="Soft drinks, coffees, teas, beers, and ales"/> <row ID="2" Name="Condiments" Description="Sweet and savory sauces, relishes, spreads, and seasonings"/> <row ID="3" Name="Confections" Description="Desserts, candies, and sweet breads"/> <row ID="4" Name="Dairy Products" Description="Cheeses"/><row ID="5" Name="Grains/Cereals" Description="Breads, crackers, pasta, and cereal"/> <row ID="6" Name="Meat/Poultry" Description="Prepared meats"/> <row ID="7" Name="Produce" Description="Dried fruit and bean curd"/> <row ID="8" Name="Seafood" Description="Seaweed and fish"/> (8 row(s) affected)
However, if the SELECT statement retrieves data from more than a single table, there is a big difference between the way the RAW and AUTO modes work. Listing 16.14 shows the same query in both formats, with the correspondent outputs.
Listing 16.14—Comparing RAW and AUTO Modes to Execute Multi-Table Queries
USE Northwind GO SELECT CategoryName, ProductName FROM Categories as Category JOIN Products as Product ON Product.CategoryID = Category.CategoryID WHERE Product.CategoryID <3 ORDER BY CategoryName, ProductName FOR XML RAW SELECT CategoryName, ProductName FROM Categories as Category JOIN Products as Product ON Product.CategoryID = Category.CategoryID WHERE Product.CategoryID <3 ORDER BY CategoryName, ProductName FOR XML AUTO
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------- <row CategoryName="Beverages" ProductName="Chai"/> <row CategoryName="Beverages" ProductName="Chang"/> <row CategoryName="Beverages" ProductName="Chartreuse verte"/> <row CategoryName="Beverages" ProductName="Côte de Blaye"/> <row CategoryName="Beverages" ProductName="Guaraná Fantástica"/> <row CategoryName="Beverages" ProductName="Ipoh Coffee"/> <row CategoryName="Beverages" ProductName="Lakkalikööri"/> <row CategoryName="Beverages" ProductName="Laughing Lumberjack Lager"/> <row CategoryName="Beverages" ProductName="Outback Lager"/> <row CategoryName="Beverages" ProductName="Rhönbräu Klosterbier"/> <row CategoryName="Beverages" ProductName="Sasquatch Ale"/> <row CategoryName="Beverages" ProductName="Steeleye Stout"/> <row CategoryName="Condiments" ProductName="Aniseed Syrup"/> <row CategoryName="Condiments" ProductName="Chef Anton's Cajun Seasoning"/> <row CategoryName="Condiments" ProductName="Chef Anton's Gumbo Mix"/> <row CategoryName="Condiments" ProductName="Genen Shouyu"/> <row CategoryName="Condiments" ProductName="Grandma's Boysenberry Spread"/> <row CategoryName="Condiments" ProductName="Gula Malacca"/> <row CategoryName="Condiments" ProductName="Louisiana Fiery Hot Pepper Sauce"/> <row CategoryName="Condiments" ProductName="Louisiana Hot Spiced Okra"/> <row CategoryName="Condiments" ProductName="Northwoods Cranberry Sauce"/> <row CategoryName="Condiments" ProductName="Original Frankfurter grüne Soße"/> <row CategoryName="Condiments" ProductName="Sirop d'érable"/> <row CategoryName="Condiments" ProductName="Vegie-spread"/> (24 row(s) affected) XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------- <Category CategoryName="Beverages"> <Product ProductName="Chai"/> <Product ProductName="Chang"/> <Product ProductName="Chartreuse verte"/> <Product ProductName="Côte de Blaye"/> <Product ProductName="Guaraná Fantástica"/> <Product ProductName="Ipoh Coffee"/> <Product ProductName="Lakkalikööri"/> <Product ProductName="Laughing Lumberjack Lager"/> <Product ProductName="Outback Lager"/> <Product ProductName="Rhönbräu Klosterbier"/> <Product ProductName="Sasquatch Ale"/> <Product ProductName="Steeleye Stout"/> </Category> <Category CategoryName="Condiments"> <Product ProductName="Aniseed Syrup"/> <Product ProductName="Chef Anton's Cajun Seasoning"/> <Product ProductName="Chef Anton's Gumbo Mix"/> <Product ProductName="Genen Shouyu"/> <Product ProductName="Grandma's Boysenberry Spread"/> <Product ProductName="Gula Malacca"/> <Product ProductName="Louisiana Fiery Hot Pepper Sauce"/> <Product ProductName="Louisiana Hot Spiced Okra"/> <Product ProductName="Northwoods Cranberry Sauce"/> <Product ProductName="Original Frankfurter grüne Soße"/> <Product ProductName="Sirop d'érable"/> <Product ProductName="Vegie-spread"/> </Category> (24 row(s) affected)
You can see from the preceding output that AUTO mode shows data in hierarchical way automatically. This suits most applications well, because in this way the user can expand the hierarchy to examine deeper levels of detail if necessary.
Using AUTO mode, you can use the ELEMENTS option to show all the attributes as elements, as in Listing 16.15
Listing 16.15—Use the ELEMENTS Option to Show Attributes As Elements
USE Northwind GO SELECT CategoryName, ProductName FROM Categories as Category JOIN Products as Product ON Product.CategoryID = Category.CategoryID WHERE Product.CategoryID <3 ORDER BY CategoryName, ProductName FOR XML AUTO, ELEMENTS
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------- <Category> <CategoryName>Beverages</CategoryName> <Product> <ProductName>Chai</ProductName> </Product> <Product> <ProductName>Chang</ProductName> </Product> <Product> <ProductName>Chartreuse verte</ProductName> </Product> <Product> <ProductName>Côte de Blaye</ProductName> </Product> <Product> <ProductName>Guaraná Fantástica</ProductName> </Product> <Product> <ProductName>Ipoh Coffee</ProductName> </Product> <Product> <ProductName>Lakkalikööri</ProductName> </Product> <Product> <ProductName>Laughing Lumberjack Lager</ProductName> </Product> <Product> <ProductName>Outback Lager</ProductName> </Product> <Product> <ProductName>Rhönbräu Klosterbier</ProductName> </Product> <Product> <ProductName>Sasquatch Ale</ProductName> </Product> <Product> <ProductName>Steeleye Stout</ProductName> </Product> </Category> <Category> <CategoryName>Condiments</CategoryName> <Product> <ProductName>Aniseed Syrup</ProductName> </Product> <Product> <ProductName>Chef Anton's Cajun Seasoning</ProductName> </Product> <Product> <ProductName>Chef Anton's Gumbo Mix</ProductName> </Product> <Product> <ProductName>Genen Shouyu</ProductName> </Product> <Product> <ProductName>Grandma's Boysenberry Spread</ProductName> </Product> <Product> <ProductName>Gula Malacca</ProductName> </Product> <Product> <ProductName>Louisiana Fiery Hot Pepper Sauce</ProductName> </Product> <Product> <ProductName>Louisiana Hot Spiced Okra</ProductName> </Product> <Product> <ProductName>Northwoods Cranberry Sauce</ProductName> </Product> <Product> <ProductName>Original Frankfurter grüne Soße</ProductName> </Product> <Product> <ProductName>Sirop d'érable</ProductName> </Product> <Product> <ProductName>Vegie-spread</ProductName> </Product> </Category> (24 row(s) affected)
You can use the XMLDATA option to produce an XML Schema in the output, as shown in Listing 16.16
Listing 16.16—Use the ELEMENTS Option to Show Attributes As Elements
USE Northwind GO SELECT CategoryName, ProductName FROM Categories as Category JOIN Products as Product ON Product.CategoryID = Category.CategoryID WHERE Product.CategoryID <3 ORDER BY CategoryName, ProductName FOR XML AUTO, XMLDATA
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------- <Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="Category" content="eltOnly" model="closed" order="many"> <element type="Product" maxOccurs="*"/> <AttributeType name="CategoryName" dt:type="string"/> <attribute type="CategoryName"/> </ElementType> <ElementType name="Product" content="empty" model="closed"> <AttributeType name="ProductName" dt:type="string"/> <attribute type="ProductName"/> </ElementType> </Schema> <Category xmlns="x-schema:#Schema2" CategoryName="Beverages"> <Product ProductName="Chai"/> <Product ProductName="Chang"/> <Product ProductName="Chartreuse verte"/> <Product ProductName="Côte de Blaye"/> <Product ProductName="Guaraná Fantástica"/> <Product ProductName="Ipoh Coffee"/> <Product ProductName="Lakkalikööri"/> <Product ProductName="Laughing Lumberjack Lager"/> <Product ProductName="Outback Lager"/> <Product ProductName="Rhönbräu Klosterbier"/> <Product ProductName="Sasquatch Ale"/> <Product ProductName="Steeleye Stout"/> </Category> <Category xmlns="x-schema:#Schema2" CategoryName="Condiments"> <Product ProductName="Aniseed Syrup"/> <Product ProductName="Chef Anton's Cajun Seasoning"/> <Product ProductName="Chef Anton's Gumbo Mix"/> <Product ProductName="Genen Shouyu"/> <Product ProductName="Grandma's Boysenberry Spread"/> <Product ProductName="Gula Malacca"/> <Product ProductName="Louisiana Fiery Hot Pepper Sauce"/> <Product ProductName="Louisiana Hot Spiced Okra"/> <Product ProductName="Northwoods Cranberry Sauce"/> <Product ProductName="Original Frankfurter grüne Soße"/> <Product ProductName="Sirop d'érable"/> <Product ProductName="Vegie-spread"/> </Category> (24 row(s) affected)
As you can see, the output from Listing 16.16 is the same as the output of the second query from Listing 16.14. The only difference is the schema information at the beginning of the output. You can use this information to create an XML-Data Reduced (XDR) schema file.
Note
Trying to read the output from Listings 16.14 to 16.16 directly in Internet Explorer will produce XML parser errors, unless you add a valid root node and save these outputs in Unicode format from Notepad.
Reading SQL Server XML Data From Active Server Pages
Although this is not an ASP book, it is interesting to show at least one example on how to write a simple Active Server Page (ASP) application to retrieve SQL Server data in XML format.
For a complete understanding of how to create Active Server Pages, you can read the book Active Server Pages 3.0 by Example (Bob Reselman, Que, ISBN 0-7897-2240-2). Working with ASP requires scripting knowledge and some understanding of HTML.
In this section you learn how to write a simple ASP to read the XML document produced from the second query in Listing 16.14.
To write the ASP you can use Notepad or any other text editor. Listing 16.17 contains the complete code for the Active Server Page SQLXML.ASP. For this code to run, you must save this file in a directory managed by Internet Information Server. In this example, you create the directory XML inside the default WWW path (c:\InetPub\wwwroot).
The script contains two main areas:
Server-Side Scripting. This section includes the main programming logic. This is where you connect to SQL Server to retrieve the information you need. This code is executed in the server computer (Internet Information Server); this code won't be sent to the client computer at all.
Client-Side Scripting. This section shows the results in the place and format you need. This code is executed in the client computer.
Both areas are properly marked in the code with comments.
Listing 16.17—A Simple Active Server Page to Read a SQL Query Using FOR XML AUTO
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" content="text/html" charset="UTF-8"/>
<TITLE>SQL-XML by Example - SQLXML.asp</TITLE>
<%
' THIS IS THE SERVER SIDE SCRIPTING
' this part won't be visible from the client side
' some constants to make the code more readable
' some of these constants are available in the adovbs.inc file
const adUseClient = 3
const adWriteChar = 0
const adWriteLine = 1
const adExecuteStream = 1024
' Create a ADO Connection object
Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")
' Define the connection string to connect to a valid SQL Server 2000 instance
' and the Northwind database. Specify client side processing,
' which in this case will be in the IIS server
' Note: if your default instance is SQL Server 7, this code will not work
' unless you installed XML support for SQL Server 7.0
Dim sConn
sConn = "Provider=sqloledb;"
sConn = sConn & "Data Source=MSSQLFGG\S2K;"
sConn = sConn & "Initial Catalog=Northwind;"
sConn = sConn & "User ID=sa"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
' Open the connection
adoConn.Open
' Create an ADO Command object,
' to send the XML query and receive the XML results
Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
' Let's start the XML request
Dim sQuery
sQuery = "<Catalog xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
sQuery = sQuery & "<sql:query>"
' This is the Query from Listing 16.14
sQuery = sQuery & "SELECT CategoryName, "
sQuery = sQuery & "ProductName "
sQuery = sQuery & "FROM Categories as Category "
sQuery = sQuery & "JOIN Products as Product "
sQuery = sQuery & "ON Product.CategoryID = Category.CategoryID "
' Note that you must change the symbol < into <
' because < is reserved in XML
' and in this code the query will be enclosed in a XML stream
sQuery = sQuery & "WHERE Product.CategoryID < 3 "
sQuery = sQuery & "ORDER BY CategoryName, ProductName "
sQuery = sQuery & "FOR XML AUTO"
' Now we can finish the XML request
sQuery = sQuery & "</sql:query></Catalog>"
' Create and open the Stream object
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
' Write the XML query into the Stream object
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
' Select the Stream object as the Command to execute
' Note the GUID for the Dialect property,
' this GUID represents the MSSQLXML format
adoCmd.CommandStream = adoStreamQuery
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
' Now we will select to send the output to the Response object
' which is a Stream object too
adoCmd.Properties("Output Stream") = Response
' We define the XML output as a Data Isle called CatalogIsle
' to be used from the client side execution
Response.write "<XML ID=CatalogIsle>"
adoCmd.Execute , , adExecuteStream
Response.write "</XML>"
%>
<SCRIPT language="VBScript" For="window" Event="onload">
' This is the client side scripting,
' and it will be viusible from the client browser
Dim xmlDoc
dim OutputXML
' Get a reference to the data island containing
' the XML results
Set xmlDoc = CatalogIsle.XMLDocument
Dim root, CChild, PChild
' Get a reference to the root node of the XML document
Set root = xmlDoc.documentElement
' Navigate through the first level of the three
' retrieving every category
' we display the CategoryName attribute in bold typeface
For each CChild in root.childNodes
OutputXML = document.all("log").innerHTML
document.all("log").innerHTML = OutputXML & "<LI><B>" &
CChild.getAttribute("CategoryName") & "</B></LI>"
' For every category,
' navigate through the next level of the three
' retrieving every product that belongs to the category
' we display the ProductName attribute in normal typeface
For each PChild in CChild.childNodes
OutputXML = document.all("log").innerHTML
document.all("log").innerHTML = OutputXML & "<UL>" &
PChild.getAttribute("ProductName") & "</UL>"
Next
Next
</SCRIPT>
</HEAD>
<BODY>
<H1>SQL-XML by Example</H1>
<H3>This is the Product Catalog for categories 1 and 2</H3>
<UL id=log>
</UL>
</BODY>
</HTML>
Looking at Listing 16.17, you can see the following line at the beginning of the file:
<META HTTP-EQUIV="Content-Type" content="text/html" charset="UTF-8"/>
It is important to specify UTF-8 (Unicode) as the character set, because the data to show in this case contains characters not available in the standard character set.
The process of this ASP is as follows:
Create a Connection object, called adoConn in this example, and connect it to SQL Server.
Create a Stream Object, called adoStreamQuery in this example, to define the XML query to be sent to SQL Server.
Create a Command object, called adoCmd in this example, to send the query to SQL Server, defined by the Stream object, and send the results directly to the Response object.
Execute the Command object, including the results in a Data Island called Catalog. In this way, the client-side scripting can easily access this data.
The client-side scripting only has a double loop to navigate through the categories and every product for each category.
You can see throughout the code many comments describing the purpose of every statement.
Opening this file with Internet Explorer (through the URL http://localhost/XML/SQLXML.ASP), you can see the same output as in Figure 16.8.
){kind=link}
Figure 16.8 SQLXML.ASP running in Internet Information Server to retrieve SQL Server data.
Now take a look at what the user could see when searching for client-side scripting. In Internet Explorer, go to the View menu and select Source. Notepad opens to show the source code for this page, as shown in Listing 16.18. As you can see in Listing 16.18, your server-side scripting is hidden from the client, including the connection string and the SQL query to retrieve the data.
Note in Listing 16.18 that the first part includes the data island containing the query results in XML format. Code comments have been automatically removed as well from the final HTML document.
Listing 16.18—This Is the Source Code of the HTML Page Returned to the Client
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" content="text/html" charset="UTF-8"/>
<TITLE>SQL-XML by Example - SQLXML.asp</TITLE>
<XML ID=CatalogIsle>
<Catalog xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Category CategoryName="Beverages">
<Product ProductName="Chai"/>
<Product ProductName="Chang"/>
<Product ProductName="Chartreuse verte"/>
<Product ProductName="Côte de Blaye"/>
<Product ProductName="Guaraná Fantástica"/>
<Product ProductName="Ipoh Coffee"/>
<Product ProductName="Lakkalikööri"/>
<Product ProductName="Laughing Lumberjack Lager"/>
<Product ProductName="Outback Lager"/>
<Product ProductName="Rhönbräu Klosterbier"/>
<Product ProductName="Sasquatch Ale"/>
<Product ProductName="Steeleye Stout"/>
</Category>
<Category CategoryName="Condiments">
<Product ProductName="Aniseed Syrup"/>
<Product ProductName="Chef Anton's Cajun Seasoning"/>
<Product ProductName="Chef Anton's Gumbo Mix"/>
<Product ProductName="Genen Shouyu"/>
<Product ProductName="Grandma's Boysenberry Spread"/>
<Product ProductName="Gula Malacca"/>
<Product ProductName="Louisiana Fiery Hot Pepper Sauce"/>
<Product ProductName="Louisiana Hot Spiced Okra"/>
<Product ProductName="Northwoods Cranberry Sauce"/>
<Product ProductName="Original Frankfurter grüne Soße"/>
<Product ProductName="Sirop d'érable"/>
<Product ProductName="Vegie-spread"/>
</Category>
</Catalog>
</XML>
<SCRIPT language="VBScript" For="window" Event="onload">
Dim xmlDoc
dim OutputXML
Set xmlDoc = CatalogIsle.XMLDocument
Dim root, CChild, PChild
Set root = xmlDoc.documentElement
For each CChild in root.childNodes
OutputXML = document.all("log").innerHTML
document.all("log").innerHTML = OutputXML & "<LI><B>" &
CChild.getAttribute("CategoryName") & "</B></LI>"
For each PChild in CChild.childNodes
OutputXML = document.all("log").innerHTML
document.all("log").innerHTML = OutputXML & "<UL>" &
PChild.getAttribute("ProductName") & "</UL>"
Next
Next
</SCRIPT>
</HEAD>
<BODY>
<H1>SQL-XML by Example</H1>
<H3>This is the Product Catalog for categories 1 and 2</H3>
<UL id=log>
</UL>
</BODY>
</HTML>
Now you can use the example in Listing 16.17 as a template to display your own queries.