Db4Breakfast: XQuery FLWOR Expression Part II

In this post we will learn how to filter and sort data in XQuery when querying XML.

-- let's assume that we have a table contain owners
-- and all the detail about the stores that they own.
USE TEMPDB;
GO
IF OBJECT_ID(N'#business', 'U') IS NOT NULL
BEGIN 
    DROP TABLE #business;
END
GO

CREATE TABLE #business (
  OwnerId INT NOT NULL,
  OwnerName VARCHAR(50) NOT NULL,
  stores XML --note: this is an XML data type
);
GO

--generate some XML data
DECLARE @bookstore1 XML = 
N'
<stores>
  <bookstore specialty="novel" address_city="Seattle">
    <book style="autobiography">
      <title>Create a Vision</title>
      <author>
        <first-name>Joe</first-name>
        <last-name>Smith</last-name>
      </author>
      <price>12</price>
    </book>
      <book style="autobiography">
      <title>Face the Sun</title>
      <author>
        <first-name>Joe</first-name>
        <last-name>Cantrell</last-name>
      </author>
      <price>20</price>
    </book>
  </bookstore>
  <bookstore specialty="science" address_city="London">
    <book style="textbook">
      <title>XML Reference</title>
      <author first-name="Mary" last-name="Jane" />
      <price>55</price>
    </book>
    <book style="novel" id="myfave">
      <title>Paths to Success</title>
      <author>
        <first-name>Toni</first-name>
        <last-name>Wilson</last-name>
      </author>
      <price intl="Canada" exchange="0.7">6.50</price>
    </book>
  </bookstore>
</stores>';

DECLARE @bookstore2 XML = 
N'
<stores>
  <bookstore specialty="computing" address_city="Seattle">
    <book style="textbook">
      <title>Advanced Game Testing</title>
      <author>
        <first-name>Sam</first-name>
        <last-name>Heng</last-name>
      </author>
      <price>20</price>
    </book>
    <book style="textbook">
      <title>XML Basic</title>
      <author>
        <first-name>Najwa</first-name>
        <last-name>Ebanks</last-name>
      </author>
      <price>30</price>
    </book>
  </bookstore>
</stores>';

--insert xml data to the table.
INSERT INTO #business(OwnerId, OwnerName, stores)
VALUES (1, 'Hai Ton', @bookstore1)
      ,(2, 'Phuong Tran', @bookstore2);

-- all books with price <= 20
-- note since there's no schema XML specified for the XML documents,
-- the price is treated as string.
-- Implicit casting occurs for the price element when the predicate $book/price <= 20 evaluates.
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20
                     return $book') AS books
FROM #business;

-- Filter with multiple predicates
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20 and $book/author/first-name = "Toni"
                     return $book')
FROM #business;

-- filter using attribute
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where  $book/@style = "textbook"
                     return $book')
FROM #business;

-- sort
-- this query will returns an error.
-- order by clause only works with a sequence of one item (a singleton),
--  thus we need to specify a number inside a [].
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20
                     order by $book/price
                     return $book')
FROM #business;

Msg 2389, Level 16, State 1, Line 8 XQuery [#business.stores.query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

-- all books with price <= 20 and sort books in ascending order by price.
-- Default sorting order is ascending. Specify descending keyword to revert sort order.
--   example: order by $price[1] descending
-- Since there's no XML schema specified for the XML documents,
--  the price is treated string thus: 6.50 > 20 > 12
-- Note: the order by clause only works with a sequence of one item (a singleton),
--  thus we need to specify a number inside a [].
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20
                     order by $book/price[1]
                     return $book')
FROM #business;

-- to work around cast price to a numeric type then sort.
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20
                     order by xs:double($book/price[1])
                     return $book')
FROM #business;

-- specify multiple sorting criteria
SELECT OwnerName,
       stores.query('for $book in stores/store/book
                     where $book/price <= 20
                     order by $book/@style[1] descending, xs:double($book/price[1]) 
                     return $book')
FROM #business;