- The EXPLICIT Mode of FOR XML
- More than One Child Tag
- Choosing the Columns for the ORDER BY Clause
- Parent Tag Not Open Error
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Choosing the Columns for the ORDER BY Clause
You might also find it challenging to order the XML hierarchy with the ORDER BY clause. You have to ensure that you have all the key columns in the ORDER BY clause to warrant the proper ordering of the results. This means including multiple columns in the ORDER BY clause from each table that has a composite key—a primary key composed of multiple columns. Sometimes, this might be useful for ordering the result set, but not exactly what you want to see in the output. For example, if you have a query joining sales and titles tables from the Pubs database, you will have to include both the stor_id and title_id columns from the sales table in the ORDER BY clause. However, because you can get the title_id from the title table, you might not wish to have this column also appear inside the <sales> tag. In such cases, you can use the "!hide" directive, which suppresses the specified column in the result set, but still lets you use it for sorting the results.
The following example is somewhat more complex than the ones you've seen so far. It takes advantage of the "!hide" directive, and has several levels of hierarchy as well as tables with composite keys:
SELECT 1 AS TAG, NULL AS PARENT,
sales.stor_id AS [sales!1!store_id],
sales.title_id AS [sales!1!title_id!hide],
sales.ord_date AS [sales!1!order_date],
sales.qty AS [sales!1!quantity],
NULL AS [store!2!store_id!hide],
NULL AS [store!2!name],
NULL AS [store!2!city_and_state],
NULL AS [title!3!title_id],
NULL AS [title!3!title],
NULL AS [titleauthor!4!author_id],
NULL AS [titleauthor!4!royalty_percentage]
FROM sales
UNION ALL
SELECT 2 AS TAG, 1 AS PARENT,
sales.stor_id AS [sales!1!store_id],
sales.title_id AS [sales!1!title_id!hide],
sales.ord_date AS [sales!1!order_date],
sales.qty AS [sales!1!quantity],
stores.stor_id AS [store!2!store_id!hide],
stores.stor_name AS [store!2!name],
stores.city + ',' +stores.state AS [store!2!city_and_state],
NULL AS [title!3!title_id],
NULL AS [title!3!title],
NULL AS [titleauthor!4!author_id],
NULL AS [titleauthor!4!royalty_percentage]
FROM sales INNER JOIN stores ON sales.stor_id = stores.stor_id
UNION ALL
SELECT 3 AS TAG, 1 AS PARENT,
sales.stor_id AS [sales!1!store_id],
sales.title_id AS [sales!1!title_id!hide],
sales.ord_date AS [sales!1!order_date],
sales.qty AS [sales!1!quantity],
stores.stor_id AS [store!2!store_id!hide],
NULL AS [store!2!name],
NULL AS [store!2!city_and_state],
titles.title_id AS [title!3!title_id],
titles.title AS [title!3!title],
NULL AS [titleauthor!4!author_id],
NULL AS [titleauthor!4!royalty_percentage]
FROM sales INNER JOIN titles ON titles.title_id = sales.title_id
INNER JOIN stores ON sales.stor_id = stores.stor_id
UNION ALL
SELECT 4 AS TAG, 3 AS PARENT,
sales.stor_id AS [sales!1!store_id],
sales.title_id AS [sales!1!title_id!hide],
sales.ord_date AS [sales!1!order_date],
sales.qty AS [sales!1!quantity],
stores.stor_id AS [store!2!store_id!hide],
NULL AS [store!2!name],
NULL AS [store!2!city_and_state],
titles.title_id AS [title!3!title_id],
titles.title AS [title!3!title],
titleauthor.au_id AS [titleauthor!4!author_id],
titleauthor.royaltyper AS [titleauthor!4!royalty_percentage]
FROM sales INNER JOIN titles ON titles.title_id = sales.title_id
INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id
INNER JOIN stores ON sales.stor_id = stores.stor_id
ORDER BY [sales!1!store_id], [sales!1!title_id!hide],
[store!2!store_id!hide], [title!3!title_id]
FOR XML EXPLICIT
Abbreviated Results:
<sales store_id="6380" order_date="2019-01-17T00:00:00" quantity="6">
<store name="Eric the Read Books" city_and_state="Seattle,WA"/>
<title title_id="BU1032" title="The Busy Executive's Database Guide">
<titleauthor author_id="213-46-8915" royalty_percentage="40"/>
<titleauthor author_id="409-56-7008" royalty_percentage="60"/>
</title>
</sales>
<sales store_id="6380" order_date="2019-01-24T00:00:00" quantity="4">
<store name="Eric the Read Books" city_and_state="Seattle,WA"/>
<title title_id="PS2091" title="Is Anger the Enemy?">
<titleauthor author_id="899-46-2035" royalty_percentage="50"/>
<titleauthor author_id="998-72-3567" royalty_percentage="50"/>
</title>
</sales>
<sales store_id="7066" order_date="1993-06-13T00:00:00" quantity="51">
<store name="Barnum's" city_and_state="Tustin,CA"/>
<title title_id="PC8888" title="Secrets of Silicon Valley">
<titleauthor author_id="846-92-7186" royalty_percentage="50"/>
<titleauthor author_id="427-17-2319" royalty_percentage="50"/>
</title>
</sales>
<sales store_id="7066" order_date="2019-01-08T00:00:00" quantity="76">
<store name="Barnum's" city_and_state="Tustin,CA"/>
<title title_id="PS2091" title="Is Anger the Enemy?">
<titleauthor author_id="899-46-2035" royalty_percentage="50"/>
<titleauthor author_id="998-72-3567" royalty_percentage="50"/>
</title>
</sales>