Home > Articles > Web Services > XML

  • Print
  • + Share This
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&apos;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&apos;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&apos;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>
  • + Share This
  • 🔖 Save To Your Account