| Downloads |
|---|
| 16551.zip |
Editor's Note: Send your XML questions to Rich Rollman at [email protected].
Your XML questions are starting to roll in! I encourage you to ask questions not only about SQL Server's XML support, but also about XML technology you might be using within your application. After all, applications involve more processes than data storage and retrieval.
How can I represent many-to-many (M:N) relationships within XML?
You most generally use XML to represent hierarchical relationships. In other words, you nest one element or a set of elements inside another element to represent a relationship between the two. For example, Listing 1 shows a hierarchical author list, which contains a list of books published by each author. But expressing relationships among entities by using references rather than nesting the entities within the body of another entity is sometimes more convenient, especially when you need to edit the data. The book Is Anger the Enemy? in Listing 1 illustrates this situation. In this case, Albert Ringer and Anne Ringer cowrote the book, so the listing shows a duplicate element for the book. Structuring the data hierarchically makes changing information about a particular book difficult because you must search for and change all instances of the book. Also, this structure can be inefficient because of the duplicate information. To address this problem, the authors of the XML specification provided a way to reference relationships between elements without using containment.
XML provides three distinct attribute types that you can use to build reference relationships. You can think of these attributes—ID, IDREF, and IDREFS—as specifying a minimal key and foreign-key relationship. Here's how the attributes work.
An XML element might have a single attribute declared as type ID (note that the name of the attribute doesn't need to be ID). This attribute's value must be a string that is unique among all other attributes of type ID used on any element in the document. Think of this value as a key value for the element where the namespace of values spans all elements in the document. In addition, an element can contain one or more attributes of type IDREF. The IDREF attribute's value must match the value of an ID attribute of an element in the XML document. You can think of an IDREF as a foreign key. Attributes of type IDREFS contain a white-space-separated list of IDREF values and enable a reference to a collection of elements. (Note that Microsoft's XML support lets you represent ID, IDREF, and IDREFS as elements in addition to attributes.) As Listing 2 shows, you can use ID and IDREF attributes together to build relationships by reference rather than by containment to represent the list of authors from Listing 1.
So, how do you generate XML that uses ID, IDREF, and IDREFS attributes? SQL Server 2000 lets you generate these attributes by using FOR XML EXPLICIT queries. Listing 3, page 70, shows the template that contains the two queries I used to generate the XML in Listing 2. Note that the queries produce inline XML Data Reduced (XDR) schemas that require Microsoft XML Parser (MSXML) 3.0 to successfully parse and validate. (You can download MSXML 3.0 at http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/000/541/msdncompositedoc.xml.) If you're not using MSXML 3.0, you need to include a Document Type Definition (DTD) or XML schema in the template to provide type information for ID and IDREFS attributes. (I pared down the complete output to fit on the page.) Attributes of type ID, IDREF, and IDREFS let XML model M:N relationships, and you can generate these attributes directly from SQL Server.
How can I build a SELECT statement into a template and display the results in a Web page (an HTML file)?
To answer the first reader's question, I used a template as a container for two queries. The template produced an XML data set of authors and books. You can use a similar process to embed data in an HTML file. Here are the steps. Using your favorite editor, create an XML file that contains the HTML you want to display in your Web page. Be careful to close all tags—XML needs to be well formed, whereas HTML doesn't. Listing 4 shows a simple HTML file.
Now, using the <query> element, embed the query inside the HTML. Note that you need to specify the xml-sql namespace so that the template processor can recognize the query tab and subsequently process the query. Also, remember to specify the FOR XML clause to obtain XML results. Listing 5 shows the complete template with the embedded query.
You need to store the template on your Web server in a virtual directory configured for template processing. (SQL Server Books Online —BOL— contains step-by-step instructions for configuring a virtual directory.)
Finally, execute the query from Microsoft Internet Explorer (IE) by entering the URL to the template. An example might be http://richroll/sqlmag/template/HTMLFromTemplate?contenttype=text/html. Note that I've included a contenttype parameter, which I needed to ensure that IE recognizes the file as HTML rather than XML. Listing 6 shows the resulting HTML.
These steps presume a literal interpretation of the reader's question because the query result was embedded directly inside the HTML. Instead, let's assume that you want to display the data within a table. You can display the data in a table by modifying the query to use table and column aliases. Listing 7 shows the modified template with the columns aliased to the table cell element and the Authors table aliased to the table row element. I also modified the template to provide the table tag instead of the xmp tag that the previous example used. Note also that I added the ELEMENTS directive to the FOR XML clause. The ELEMENTS directive causes SQL Server to return the columns selected in the query as sub-elements instead of attributes. This approach lets you return the values of the columns inside table cells.
An alternative methodology for building a Web page from a query result is to use Extensible Style Language Transformations (XSLT) to transform the query result into a Web page. Using XSLT provides greater flexibility at the cost of complexity and speed.