-----------------------------------------------------------------------------
-- (c) Copyright IBM Corp. 2007 All rights reserved.
-- 
-- The following sample of source code ("Sample") is owned by International 
-- Business Machines Corporation or one of its subsidiaries ("IBM") and is 
-- copyrighted and licensed, not sold. You may use, copy, modify, and 
-- distribute the Sample in any form without payment to IBM, for the purpose of 
-- assisting you in the development of your applications.
-- 
-- The Sample code is provided to you on an "AS IS" basis, without warranty of 
-- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
-- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
-- not allow for the exclusion or limitation of implied warranties, so the above 
-- limitations or exclusions may not apply to you. IBM shall not be liable for 
-- any damages you suffer as a result of using, copying, modifying or 
-- distributing the Sample, even if IBM has been advised of the possibility of 
-- such damages.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: sqlxquery.db2
--
-- SAMPLE: SQL/XML Queries 
--
-- SQL/XML FUNCTIONS USED
--          sqlquery
--          xmlexists
--          xmlquery 
--
-- SQL STATEMETNS USED
--          SELECT 
--
-- SAMPLE EXECUTION:
-- Run the samples with following command
--    db2 -td@ -vf sqlxquery.db2
--
--                           sqlxquery           xpath          
-----------------------------------------------------------------------------
--
-- For more information about the command line processor (CLP) scripts,
-- see the README file.
--
-- For information on using XQUERY statements, see the XQUERY Reference.
--
-- For information on using SQL statements, see the SQL Reference.

-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
-----------------------------------------------------------------------------

-- Connect to sample database
CONNECT TO SAMPLE@

-- Find out first purchaseorders of the customer with name Robert Shoemaker 
SELECT XMLQUERY('$p/PurchaseOrder/item[1]' PASSING p.porder AS "p") 
     FROM purchaseorder AS p, customer AS c
     WHERE XMLEXISTS('$custinfo/customerinfo[name="Robert Shoemaker" and @Cid = $cid]'  
                     PASSING c.info AS "custinfo", p.custid AS "cid")@
 
-- Return the first item in the purchaseorder and the history of all the customer 
-- when the following conditions are met
-- 1. Customer ID in the sequence (1000,1002,1003) or
-- 2. Name is sequece (X,Y,Z)
SELECT XMLQUERY('$p/PurchaseOrder/item[1]' passing p.porder as "p"),XMLQUERY('$x/history' passing c.history as "x") 
       FROM purchaseorder as p,customer as c  
       WHERE XMLEXISTS('$custinfo/customerinfo[name=(X,Y,Z) or @Cid=(1000,1002,1003) and @Cid=$cid ]'
                        PASSING c.info AS "custinfo", p.custid AS "cid")@

-- Find out all the customer names and sort them according to number of orders
WITH count_table AS ( SELECT count(poid) AS c,custid 
                FROM purchaseorder,customer 
                WHERE cid=custid GROUP BY custid ) 
     SELECT c,custid, XMLQUERY('$s/customerinfo[@Cid=$id]/name' 
                                PASSING customer.info AS "s", count_table.custid AS "id") 
     FROM customer,count_table 
     WHERE custid=cid ORDER BY custid@

-- Find out the number of purchaseorder having item with partid 100-101-01 for customer Robert Shoemaker 
WITH cid_table AS (SELECT Cid FROM customer 
                   WHERE XMLEXISTS('$custinfo/customerinfo[name="Robert Shoemaker"]' PASSING customer.info AS "custinfo")) 
     SELECT count(poid) FROM purchaseorder,cid_table 
     WHERE XMLEXISTS('$po/itemlist/item[partid="100-101-01"]' PASSING purchaseorder.porder AS "po") 
                 AND purchaseorder.custid=cid_table.cid@ 

-- Reset the connection
CONNECT RESET@