-- Expressions using the OrderXML1 xml data type column
-- Paste the expression into the upper text box 
-- Click Execute XQuery Expression

-- XQuery 1: Last Order by OrderID element predicate (not FLWOR)

SELECT OrderXML1.query('(/Order[/Order/OrderID=11077])') FROM Orders;

-- XQuery 2A: Inefficient XPath expression that returns 820 empty nodes
-- to an XmlReader before reaching the desired nodes

SELECT OrderXML1.query('(/Order[./OrderID >= 11067 and ./OrderID <= 11077])') 
AS [Order] FROM Orders;

-- XQuery 2B: Efficient version of 2A that returns only the 10 desired nodes

SELECT OrderXML1.query('/Order') 
FROM Orders 
WHERE OrderXML1.exist('/Order/OrderID[.>= 11068 and .<= 11077]') = 1;

-- XQuery 3: Simple FLWOR expression to return all 830 Order documents

SELECT OrderXML1.query('for $Result in /Order return $Result') FROM Orders;


-- XQuery 4: FLWOR that uses the 'exists' method as a WHERE clause qualifier

SELECT OrderXML1.query('for $Result in /Order return $Result') FROM Orders WHERE OrderXML1.exist('/Order[data(ShipCountry)="USA"]') = 1;

-- XQuery 5: FLWOR expression with a range scan
-- Uses xs:date and multiple WHERE criteria for a date range (tests secondary indexes)
-- Adds renamed fields from the Customers table with a value-based join, sql:column,
-- and an if ... then ... else conditional expression 

SELECT OrderXML1.query('for $Result in /Order return 
<Order>
  <OrderDate>{data($Result/OrderDate)}</OrderDate>
  <OrderID>{data($Result/OrderID)}</OrderID>
  <ShipName>{data($Result/ShipName)}</ShipName>
  <ShipPhone>{sql:column("Customers.Phone")}</ShipPhone>
  {if (not(empty(sql:column("Customers.Fax")))) then 
     <ShipFax>{sql:column("Customers.Fax")}</ShipFax>
   else ()} 
</Order>') 
FROM Orders, Customers WHERE 
OrderXML1.exist('/Order/OrderDate[.>= xs:dateTime("1996-07-10T00:00:00Z") and 
  .<= xs:dateTime("1997-07-10T00:00:00Z")]') = 1 AND 
Customers.CustomerID = OrderXML1.value('(/Order/CustomerID)[1]', 'nvarchar(5)'); 

-- XQuery6: Examples of count, sum, average, round, concat, and string functions 

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; for $Result in /nwso:SalesOrder return 
<SalesOrder> 
  <SalesOrderNum>{data($Result/@nwso:OrderID)}</SalesOrderNum> 
  <SalesOrderDate>{data($Result/@nwso:OrderDate)}</SalesOrderDate> 
  <LineItemsCount>{count($Result/nwso:LineItems/nwso:LineItem)}</LineItemsCount> 
  <LineItemsValue>
    {concat("$", xs:string(round(sum($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}
  </LineItemsValue> 
  <LineItemsAverage>
    {concat("$", xs:string(round(avg($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}
  </LineItemsAverage> 
</SalesOrder>') FROM Orders; 

-- XQuery 7A: Replaces ShipVia value with 1 in specified OrderXML1 documents
-- Use XQuery 7B for xs:int type if the Orders table's OrderXML1 column has a schema.
-- CAUTION: This query makes a permanent change to the Northwind database
-- Use XQuery14 if you don't want to change the ShipVia field values

UPDATE Orders SET OrderXML1.modify('replace value of (/Order/ShipVia/text())[1] with "1"') WHERE ShipCountry = 'USA' AND OrderXML1 IS NOT NULL;

-- XQuery7B: Replaces ShipVia value with 1 in specified OrderXML1 documents

-- Uses xs:int type because the Orders table's OrderXML1 column has a schema.
-- Run Sample XQuery Expression 1 to verify the change

UPDATE Orders SET OrderXML1.modify('replace value of (/Order/ShipVia)[1] with xs:int(1)') WHERE ShipCountry = 'USA' AND OrderXML1 IS NOT NULL;


-- Expressions using the OrderXML2 xml data type column (three namespaces)

-- XQuery8: Last Order by OrderID attribute predicate (not FLWOR)

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; (/nwso:SalesOrder[/nwso:SalesOrder/@nwso:OrderID=11077])') FROM Orders;

-- XQuery9: Simple FLWOR expression to return all 830 Order documents

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; for $Result in /nwso:SalesOrder return $Result') FROM Orders;

-- XQuery10: Uses the 'exists' method as a WHERE clause qualifier
-- Uses multiple namespaces with the 'exists' method

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder" declare namespace nwbt="http://www.northwind.com/schemas/BillTo"; for $Result in /nwso:SalesOrder/nwbt:BillTo return $Result') FROM Orders WHERE OrderXML2.exist('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder" declare namespace nwbt="http://www.northwind.com/schemas/BillTo"; /nwso:SalesOrder/nwbt:BillTo[data(nwbt:Country)="USA"]') = 1;

-- XQuery11: Uses xs:date and multiple WHERE criteria for a date range (tests secondary indexes)
-- Adds renamed fields from the Customers table with a value-based join, sql:column,
-- and an if ... then ... else conditional expression 

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder" declare namespace nwbt="http://www.northwind.com/schemas/BillTo"; for $Result in /nwso:SalesOrder return 
<SalesOrder>
  <SalesOrderDate>{data($Result/@nwso:OrderDate)}</SalesOrderDate>
  <SalesOrderNum>{data($Result/@nwso:OrderID)}</SalesOrderNum>
  <Name>{data($Result/nwbt:BillTo/nwbt:Name)}</Name>
  <ShipPhone>{sql:column("Customers.Phone")}</ShipPhone>
  {if (not(empty(sql:column("Customers.Fax")))) then 
     <ShipFax>{sql:column("Customers.Fax")}</ShipFax>
   else ()} 
</SalesOrder>') 
FROM Orders, Customers WHERE 
OrderXML2.exist('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; /nwso:SalesOrder/@nwso:OrderDate[.>= xs:dateTime("1996-07-10T00:00:00Z") and 
  .<= xs:dateTime("1997-07-10T00:00:00Z")]') = 1 AND 
Customers.CustomerID = OrderXML2.value('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; (/nwso:SalesOrder/@nwso:CustomerID)[1]', 'nvarchar(5)'); 

-- XQuery12: Examples of count, sum, average, round, concat, and string functions 

SELECT OrderXML2.query('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; for $Result in /nwso:SalesOrder return 
<SalesOrder> 
  <SalesOrderNum>{data($Result/@nwso:OrderID)}</SalesOrderNum> 
  <SalesOrderDate>{data($Result/@nwso:OrderDate)}</SalesOrderDate> 
  <LineItemsCount>{count($Result/nwso:LineItems/nwso:LineItem)}</LineItemsCount> 
  <LineItemsValue>
    {concat("$", xs:string(round(sum($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}
  </LineItemsValue> 
  <LineItemsAverage>
    {concat("$", xs:string(round(avg($Result/nwso:LineItems/nwso:LineItem/nwso:Extended))))}
  </LineItemsAverage> 
</SalesOrder>') FROM Orders; 

-- XQuery 13: Replaces ShipperID attribute value with 1 in specified OrderXML2 documents
-- Use XQuery14 with xs:unsignedByte type if the Orders table's OrderXML2 column has a schema.

UPDATE Orders SET OrderXML2.modify('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; replace value of (/nwso:SalesOrder/@nwso:ShipperID)[1] with "1"') WHERE ShipCountry = 'USA' AND OrderXML2 IS NOT NULL;

-- XQuery14: Replaces ShipperID attribute value with 1 in specified OrderXML2 documents
-- Uses xs:unsignedByte type because the Orders table's OrderXML2 column has a schema.

UPDATE Orders SET OrderXML2.modify('declare namespace nwso="http://www.northwind.com/schemas/SalesOrder"; replace value of (/nwso:SalesOrder/@nwso:ShipperID)[1] with xs:unsignedByte(1)') WHERE ShipCountry = 'USA' AND OrderXML2 IS NOT NULL;
