Writing XQuery Queries In SQL Server 2012

SQL Server 2012 implement XQuery using several methods for XML data type.
To simplify the examples below, I insert some XML data to a XML column within a table. Then use the SQL Server query() method to write some simple XQuery queries. From last post we learned that XQuery queries return sequences, so I will use the word "sequence" for the result of the examples below.

-- 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);

--get all rows in the table
SELECT OwnerId, 
       OwnerName, 
       stores
FROM   #business;

--
--to use XQuery query in SQL Server, we put XQquery inside of the query() method.
--note: this case, the XQuery query is just a simple XPath Expression that
--      returns all data in the XML column.
SELECT OwnerName, 
       stores.query('//stores') AS XML_Result
FROM   #business;

--
--get the first book in each bookstore
--Since the first owner has 2 bookstores, there'll 2 books 
--   return for that owner's bookstores
SELECT OwnerName, 
       stores.query('stores/bookstore/book[1]') AS XML_Result
FROM   #business;

--
--get all books with price <= $5.0 regardless of the currency.
--result is an empty sequence for the bookstore column
SELECT OwnerName, 
       stores.query('stores/bookstore/book[price <= 5.0]') AS XML_Result
FROM   #business;

--
--get all books with price <= $20.0 regardless of the currency
SELECT OwnerName, 
       stores.query('stores/bookstore/book[price <= 20.0]') AS XML_Result
FROM   #business;

--
--unlike query above, this query returns true or false as XML.
--because we did not use the [] for the predicate
SELECT OwnerName, 
       stores.query('stores/bookstore/book/price <= 20.0') AS XML_Result
FROM   #business;

--
--get all books with price >= $30.0 regarless of the currency, 
-- or the author first name = "Toni"
SELECT OwnerName,
       stores.query('stores/bookstore/book[price >= 30.0 or ./author/first-name="Toni"]') AS XML_Result
FROM   #business;

Reference:
W3c XQuery 3.0