Now, let's examine the more complex solution to creating an XML document that contains columns from different XML hierarchy levels in the same table: the FOR XML EXPLICIT query. Listing 3 contains the FOR XML EXPLICIT query that returns the XML result that Figure 3 shows. The result in Figure 3 is almost identical to the result in Figure 1, with two exceptions. First, Listing 3 returns all the rows, instead of just the first five, because you can't use the TOP clause in a FOR XML EXPLICIT query that has a UNION operator. Second, the FOR XML EXPLICIT query selects all authors, not just the authors who have titles.
The query in Listing 3 consists of two SELECT clauses: The first retrieves the author data, and the second retrieves the titles for each author. These two clauses gather all the data you need to generate the result. The ORDER BY clause sorts the result to position all rows for one author together and ensures that the rows containing titles for an author follow that author's name row. This structure enables the XML post-processor to nest Title tags within the correct Author tags.
Special structured column names specify the names of the XML elements and attributes in the XML result. Each column name consists of four fields and uses an exclamation point as the field separator. For example, [Author!1!FirstName] from the query in Listing 3 shows the first three fields and an implied fourth field. The first field specifies the containing element's name (e.g., Author); this XML element contains the selected column's values. The second field specifies the Tag number for the element (e.g., 1); the Tag number refers to the parent element in the hierarchy and corresponds to the value in the Parent column. The third field is the name of the attribute or contained element that holds this column's value in the SELECT clause (e.g., FirstName). The fourth field is a directive to the XML post-processor about how to represent the XML output value; directives include representing a value as an attribute, a subelement, or a CDATA section. (For more information about directives, see SQL Server 2000 Books Online—BOL.) By combining the values of the third and fourth fields, you can produce the result in Figure 4.
The code in Listing 4 modifies the FOR XML EXPLICIT query from Listing 3 to output the book's title as the Title element's content. In the query in Listing 4, I changed the title column name from [Title!2!Name] to [Title!2!!element]. I set the third field to NULL to show that the output contains no attribute or element name. The directive in the fourth field tells the XML post-processor to represent the value as a Title subelement. However, the value appears as the content of the Title element because the third field doesn't supply a name. This structure produces the desired result, as Figure 3 shows.
You can use the techniques in this answer to build complex data sets. For example, the query in Listing 5 combines the functionality of the queries in Listings 3 and 4 to produce an XML result in which the Author element contains an Address subelement and zero or more Title subelements, one for each title by the author, as Figure 4 shows. Note that unlike a FOR XML AUTO query, a FOR XML EXPLICIT query doesn't require a SELF-JOIN to produce subelements from the same table because you can use multiple SELECT clauses with the UNION operation to obtain a similar result.
End of Article
Prev. page
1
[2]
next page -->
