Creating a Document That Has Columns from Different Hierarchy Levels
Editor's Note: Send your XML questions to Rich Rollman at xmlquestions@sqlmag.com.
Using a SQL query, can I create an XML document that has columns from different XML hierarchy levels in the same table? Then, when I have that XML document, can I place data from one column into an element's content even though the other columns are attributes of that element?
When querying a relational database, the underlying table structure rarely matches the query's exact shape. To obtain the desired result, you write SQL queries that perform JOINs and restructure the result the way you desire. This statement is even truer for XML. Because you can structure XML documents with arbitrary hierarchies, you might need to represent data stored in one database table at different hierarchy levels in an XML result. Many ways of obtaining the desired result are possible. Let's focus on using FOR XML queries. You can create this document in two ways—one simple and one more complex (albeit more flexible)—by using SQL Server 2000's FOR XML clause. The simple solution uses a FOR XML AUTO query with one or more SELF-JOINs to produce an XML result that contains columns from different nested elements of the same table. The more complex solution uses a FOR XML EXPLICIT query, which is harder to write but provides more flexibility in the structure of the XML result that the query produces. How you want the XML result structured determines which solution you choose.
First, let's explore the simple solution. When processing a FOR XML AUTO query, the XML post-processor built into SQL Server 2000's query engine uses either the names of the tables and columns in the query or arbitrary names that use table or column aliases to name the XML elements and attributes. The XML query's structure is based on the order in which the columns occur in the SELECT clause. If the SELECT clause contains a column from a different table, the output includes a new, separate subelement. Listing 1 shows the approach with a query that uses the Pubs database. For the top five publications, the query outputs the author's name, the publication's title, and its price, as Figure 1 shows.
You can also use a FOR XML AUTO query to produce an Author element with a nested Address element that contains the author's address information, as the following sample result shows:
<Author FirstName="Rich" LastName="Rollman">
<Address Street="One Main Street" City="Sammamish"
State="WA" Zip="98075"/>
</Author>
The query in Listing 2, page 62, a modification of the query from Listing 1, uses a SELF-JOIN to retrieve the author's address fields. You use table aliases to name two instances of the Authors table: one for the Author and one for the Address. The XML post-processor uses the alias names and treats the two aliases as separate tables. The result is two separate elements: Author and Address. The column order in the SELECT clause determines the element nesting order in the result. Because a column from the Author alias appears first in the SELECT clause, Author becomes the top-level element with Address elements nested within the Author elements, as Figure 2 shows.
Because a FOR XML AUTO query is easy to write and maintain, it should always be your first choice for generating an XML result from SQL Server. Unfortunately, a FOR XML AUTO query can't produce a result in which the values of some columns are attributes and the values of other columns are subelements. For example, consider the following XML fragment, which modifies the result in Figure 1:
<Author FirstName="Rich" LastName="Rollman">
<Title Price="0.0000">
XML Q&A
</Title>
</Author>
In this fragment, the publication title, "XML Q&A," is the Title element's content, but Price is an attribute. To produce this kind of XML result, you need a FOR XML EXPLICIT query.
Prev. page
[1]
2
next page 
