数据库-研究生课件：Viper-XML-XQuery

1、IBM Software Group IBM CorporationDB2 Viper TechnologyXQuery and SQL/XMLWorldwide Information Management Technical PresalesIBM Software Group 2006 IBM Corporation2Disclaimer/TrademarksInformation concerning non-IBM products was obtained from the suppliers of those products, their published announcem

2、ents, or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility, or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.All st

3、atements regarding IBMs future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only. This information may contain examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples incl

4、ude the names of individuals, companies, brands, and products. All of these names are fictitious, and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.TrademarksThe following terms are trademarks or registered trademarks of other companies and

5、have been used in at least one of the pages of the presentation:The following terms are trademarks of International Business Machines Corporation in the United States, other countries, or both: AIX, AS/400, DataJoiner, DataPropagator, DB2, DB2 Connect, DB2 Extenders, DB2 OLAP Server, DB2 Universal D

6、atabase, Distributed Relational Database Architecture, DRDA, eServer, IBM, IMS, iSeries, MVS, Net.Data, OS/390, OS/400, PowerPC, pSeries, RS/6000, SQL/400, SQL/DS, Tivoli, VisualAge, VM/ESA, VSE/ESA, WebSphere, z/OS, zSeriesMicrosoft, Windows, Windows NT, and the Windows logo are trademarks of Micro

7、soft Corporation in the United States, other countries, or both.Intel and Pentium are trademarks of Intel Corporation in the United States, other countries, or both.UNIX is a registered trademark of The Open Group in the United States and other countries.Java and all Java-based trademarks are tradem

8、arks of Sun Microsystems, Inc. in the United States, other countries, or both.Other company, product, or service names may be trademarks or service marks of others.IBM Software Group 2006 IBM Corporation3Two WorldsXQueryStandardSQL/XMLStandardsSQLXQueryIBM Software Group 2006 IBM Corporation4DB2 and

9、 XQuery Using XQuery as the primary language4 All queries begin with XQuery4 Tells the DB2 parser what to expect4 Can execute SQL within a query beginning with XQUERY All XML data is in XML typed columns in tables DB2 XQuery uses 2 XQuery functions to retrieve data:4 db2-fn:xmlcolumn()4 db2-fn:sqlqu

10、ery() XML characteristics 4 CASE SENSITIVE . Toronto does not equal toronto!4 Brackets are critical4 Quotes are critical4 Use # as command termination character not , $, ;, %, IBM Software Group 2006 IBM Corporation5XQuery XPath SamplesXQUERYResultXQUERY db2-fn:xmlcolumn (PEOPLE.INFO) /dept/employee

11、id=902/namePeter PanXQUERY db2-fn:xmlcolumn (PEOPLE.INFO)/deptbldg=101/employeeoffice 300/nameJohn DoeXQUERY db2-fn:xmlcolumn (PEOPLE.INFO)/employeeoffice=344 OR office=216/id901902XQUERY db2-fn:xmlcolumn (PEOPLE.INFO)/dept/employee2/id902 John Doe408 555 1212344Peter Pan408 555 9918216Table PEOPLEC

12、olumn INFOXQUERY db2-fn:xmlcolumn (PEOPLE.INFO)Returns all Documents in column INFOXQUERY db2-fn:sqlquery (select info from people)Returns all Documents in column INFOIBM Software Group 2006 IBM Corporation6DB2 SQL with XML Using SQL as the primary language4Looking at query only4All queries begin wi

13、th Select4XML Retrieval done with XMLQUERY (flags db2 parser to switch to XML Processing) New SQL XML functions introduced to the languageIBM Software Group 2006 IBM Corporation7New SQL/XML Functions in SQL 2006 XMLPARSE4parses character/BLOB data, produces XML value XMLSERIALIZE4converts an XML val

14、ue into character/BLOB data XMLVALIDATE4validates XML value against an XML schema and type-annotates the XML value XMLEXISTS4determines if an XQuery returns a results (i.e. a sequence of one or more items) XMLQUERY4executes an XQuery and returns the result sequence XMLTABLE4executes an XQuery, retur

15、ns the result sequence as a relational table (if possible) XMLCAST4cast to or from an XML typeIBM Software Group 2006 IBM Corporation8XMLQUERYselect xmlquery($ColName/dept/employeeid=901/office passing info as ColName) from dept; John Doe408 555 1212344Peter Pan408 555 9918216Table PEOPLEColumn INFO

16、344Answer Select iterates over all rows in the dept tableFor each row, xmlquery is invoked4 The passing clause binds the variable $ColName to the value of the info column of the current row4 The xquery expression is executed4 Xmlquery returns the result of the xquery expression4 Xmlquery returns a v

17、alue of type XMLIBM Software Group 2006 IBM Corporation9XMLQUERYselect EmpStatus, xmlquery($ColName/dept/employeeid300/name passing info as ColName) from people where EmpStatus=Retired; John Doe408 555 1212344Table PEOPLE2 Columns:EmpStatus Char(10)Info XMLFor each rowSelect iterates over all rows i

18、n the dept table4 EmpStatus is evaluated to see if it equals Retired4 If it does then xmlquery is invoked4 The passing clause binds the variable $ColName to the value of the info column of the current row4 The xquery expression is executed4 Xmlquery returns the result of the xquery expression4 Xmlqu

19、ery returns a value of type XML4 Result set includes EmpStatus and result of xquery expressionIBM Software Group 2006 IBM Corporation10XMLExists Parameter syntax is identical to XMLQuery Used as predicates in SQL where clauses Returns false if XQuery returns empty sequence, true otherwiseSELECT * FR

20、OM T WHERE XMLEXISTS($R/A/B PASSING DOC AS R)4 row is selected if doc contains at least one b-element under a.SELECT * FROM T WHERE XMLEXISTS($R/AC=5 PASSING DOC AS R)4 row is selected if doc contains at least one a-element with a c-attribute equal to 5.SELECT * FROM T WHERE XMLEXISTS($R/A/C=5 PASSI

21、NG DOC AS R)4 row is always selected because XQuery never returns empty sequenceNot intuitive!IBM Software Group 2006 IBM Corporation11Table: XMLCustomer Table, Column: INFO ValueValueValueValueValueValueIBM Software Group 2006 IBM Corporation12Table: XMLCUSTOMER, Column: INFOcustomerinfoaddrphonena

22、mecountry=valuevalueValueType=valuestreetcityprov-statepcode-zipValueValueValueValueIBM Software Group 2006 IBM Corporation13Sample XQueries (1) Use XQUERY and db2-fn:xmlcolumn to retrieve all the XML documentsXQUERY db2-fn:xmlcolumn(XMLCUSTOMER.INFO);IBM Software Group 2006 IBM Corporation14Sample

23、XQueries (2) Use XQUERY and db2-fn:sqlquery to retrieve all the XML documents XQUERY db2-fn:sqlquery(select info from xmlcustomer);IBM Software Group 2006 IBM Corporation15Sample XQueries (3) Use XQUERY and db2-fn:xmlcolumn to retrieve all the names of customers where the addr, city is TorontoXQUERY

24、 db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfoaddr/city=Toronto/name;IBM Software Group 2006 IBM Corporation16Sample XQueries (4) Use XQUERY and db2-fn:xmlcolumn to retrieve the city where the country is CanadaXQUERY db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/addrcountry=Canada/city;IBM Software Group 200

25、6 IBM Corporation17Sample XQueries (5) Count the number of customers locations in Toronto. This uses XQUERY and db2-fn:xmlcolumn and count4 XQuery count(whatever it is you want to count)XQUERY count(db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfoaddr/city =Toronto);IBM Software Group 2006 IBM Corpor

26、ation18Sample SQL/XML Queries (6) Use SQL to retrieve the customer ids (CID) and XML Documents if the name is Kathy Smith4 xmlexists() Selectc.cid, from xmlcustomer cwhere xmlexists($i/customerinfoname = Kathy Smith passing as i);IBM Software Group 2006 IBM Corporation19Sample SQL/XML Q

27、ueries (7) Use SQL as the primary language to retrieve the customer name where the customer id (CID) is 1002.select xmlquery($i/customerinfo/name passing as i) from xmlcustomer c where c.cid = 1002;IBM Software Group 2006 IBM Corporation20Sample SQL/XML Queries (8) Using XQuery find the custo

28、mer name that has a work phone number of 905-555-7258XQUERY db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo/phonetype=work and text()=905-555-7258/./name ;IBM Software Group 2006 IBM Corporation21Sample SQL/XML Queries (9) Using SQL find the customer name that has a work phone number of 905-555-7258

29、select xmlquery($i/customerinfo/name passing as i)from xmlcustomer c where xmlexists($i/customerinfophone = 905-555-7258 and phone/type = work passing as i) ;IBM Software Group 2006 IBM Corporation22Sample SQL/XML Queries (10) Use XQUERY and db2-fn:sqlquery to retrieve all customers wh

30、ere the customer ID (CID) equals 1002XQUERY db2-fn:sqlquery(select info from xmlcustomer where cid = 1002);IBM Software Group 2006 IBM Corporation23Sample XQuery with XML Structure (11) Use XQUERY and db2-fn:sqlquery to create a single XML document which contains all customers where the customer ID

31、(CID) equals 10024 Form of query will be:XQUERY whatever you want included in the document XQUERY db2-fn:sqlquery(select info from xmlcustomer where cid = 1002) ;IBM Software Group 2006 IBM Corporation24XQuery: The FLWOR Expression FOR: iterates through a sequence, bind variable to items LET: binds

32、a variable to a sequence WHERE: eliminates items of the iteration ORDER: reorders items of the iteration RETURN: constructs query results create table dept(deptID char(8),deptdoc xml);xqueryfor $d in db2-fn:xmlcolumn(dept.deptdoc)/deptlet $emp := $d/employee/namewhere $d/bldg 95 order by $d/bldgretu

33、rn $d/bldg, $emp John Doe408 555 1212344Peter Pan408 555 9918216Input:IBM Software Group 2006 IBM Corporation25John DoePeter Pan John Doe Peter PanFLWOR ExpressionJohn DoePeter Panxqueryfor $d in xmlcolumn(deptdoc)/deptwhere $d/bldg = 101return $d/employee/name This result is not an XML document!xqu

34、eryfor $d in xmlcolumn(deptdoc)/deptwhere $d/bldg = 101return $d/employee/name/text()xqueryfor $d in xmlcolumn(deptdoc)/deptwhere $d/bldg = 101return $d/employee/name John Doe408 555 1212344Peter Pan408 555 9918216create table dept (deptID char(8), deptdoc xml);IBM Software Group 2006 IBM Corporatio

35、n26XQuery Join over 2 XML Columns Nested FLWOR:XQUERYfor $book in db2-fn:xmlcolumn(BOOKS.DOC)/book for $entry in db2-fn:xmlcolumn(REVIEWS.DOC)/entry where $book/title = $entry/title return $entry/review/text() ;IBM Software Group 2006 IBM Corporation27FLWOR Queries (12) Use an XQUERY FLWOR (using ju

36、st for and return) which lists all the customer names.XQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo return $customer/nameIBM Software Group 2006 IBM Corporation28FLWOR Queries (13) Use an XQUERY FLWOR which lists all the customer names in alphabetical order.XQUERY for $cust

37、omer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo order by $customer/name return $customer/nameIBM Software Group 2006 IBM Corporation29FLWOR Queries (14) Use an XQUERY FLWOR which lists all the customer names in alphabetical order but strips out all the XML tags.4 xs:string() might be helpful

38、 here4 Assigning things to variables might help:let $variable := somethingXQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo let $cname := xs:string($customer/name) order by $cname return $cnameIBM Software Group 2006 IBM Corporation30FLWOR Queries (15) Use an XQUERY FLWOR which

39、 lists all the customer names in alphabetical order along with their address. Include the XML tags in the output.4 return (onething, anotherthing) might be helpful hereXQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo order by $customer/name return ($customer/name, $customer/ad

40、dr)IBM Software Group 2006 IBM Corporation31FLWOR Queries on the Customer Table (16) List all the customer names in alphabetical order along with their address. Only list the customers who live in the city of Markham. Include the XML tags in the output.4 Hint: you knew there was a reason for the W i

41、n FLWOR.XQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo where $customer/addr/city = Markham order by $customer/name return ($customer/name, $customer/addr) IBM Software Group 2006 IBM Corporation32FLWOR Queries on the Customer Table (17) List all the customer names in alphabe

42、tical order along with their address. Select only the customers who live in the cities of Markham or San Jose. Include the XML tags in the output.XQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo where $customer/addr/city = Markham or $customer/addr/city = San Jose order by $cu

43、stomer/name return ($customer/name, $customer/addr) IBM Software Group 2006 IBM Corporation33FLWOR Queries on the Customer Table (18) List all the customer names in alphabetical order along with their address. List all the customers who live in Canada. Include the XML tags in the output.XQUERY for $

44、customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo where $customer/addr/country = Canada order by $customer/name return ($customer/name, $customer/addr) IBM Software Group 2006 IBM Corporation34FLWOR Queries on the Customer Table (19) List all the customer names in alphabetical order along

45、with what city they live in. List all the customers who live in Canada. Do not include the XML tags in the output.XQUERY for $customer in db2-fn:xmlcolumn(XMLCUSTOMER.INFO)/customerinfo let $cname := xs:string($customer/name) let $ccity := xs:string($customer/addr/city) where $customer/addr/country

46、= Canada order by $customer/name return ($cname, $ccity) IBM Software Group 2006 IBM Corporation35FLWOR Queries on the Product Table (20) List all the product names ordered by price. Do not include the XML tags in the output.XQUERY for $prod in db2-fn:xmlcolumn(XMLPRODUCT.DESCRIPTION)/product/descri

47、ption let $pname := xs:string($prod/name) let $pprice := xs:string($prod/price) order by $pprice return ($pname, $pprice)IBM Software Group 2006 IBM Corporation36FLWOR Queries on the Product Table (21) List all the product names ordered by price. Make sure the prices are in numeric order. Do not inc

48、lude the XML tags in the output.4 Hint: xs:decimal() might help hereXQUERY for $prod in db2-fn:xmlcolumn(XMLPRODUCT.DESCRIPTION)/product/description let $pname := xs:string($prod/name) let $pprice := xs:decimal($prod/price) order by $pprice return ($pname, $pprice)IBM Software Group 2006 IBM Corpora

49、tion37FLWOR Queries on the Product Table (22) Lists some product names and prices ordered by price. Only select the products with prices that are 12 or greater and less than 20. Make sure the prices are in numeric order. Construct new XML tags for the output like: the product name the price XQUERY f

50、or $prod in db2-fn:xmlcolumn(XMLPRODUCT.DESCRIPTION)/product/description let $pname := xs:string($prod/name) let $pprice := xs:decimal($prod/price) where $pprice = 12 and $pprice 20 order by $pprice return $pname $pprice IBM Software Group 2006 IBM Corporation38XMLTable: make table from XMLSELECT X.

51、* from XMLTABLE (db2-fn:xmlcolumn(PORDERS.PO)/customer COLUMNS CID INTEGER PATH id, Name VARCHAR(30) PATH name, ZipType CHAR(2) PATH zip/type, Zip XML PATH zip ) AS X95023USnull471133129USBobby1325ZipZipTypeNameCIDEach item in the sequence returned by the XQuery will result in a rowThe row data is c

52、reated with that item as contextIBM Software Group 2006 IBM Corporation39XMLTable Example (23) XMLTable example on Customer tableSELECT X.* FROM XMLCUSTOMER C, XMLTable($cu/customerinfo PASSING C.INFO as cuCOLUMNS NAME CHAR(20)PATH name,STREET CHAR(20)PATH addr/street,CITY CHAR(20)PATH addr/city) AS

53、 XIBM Software Group 2006 IBM Corporation40XMLTable Examples (24) Add a column for country to the previous XMLTable exampleSELECT X.* FROM XMLCUSTOMER C, XMLTable($cu/customerinfo PASSING C.INFO as cu COLUMNS NAME CHAR(20) PATH name, STREET CHAR(20) PATH addr/street, CITY CHAR(20) PATH addr/city, CO

54、UNTRY CHAR(20)PATH addr/country ) AS XIBM Software Group 2006 IBM Corporation41XMLTable Examples (25) Modify the previous example to output only the name, city and country columns, but dont change the columns in the XMLTable definition.4 Select only the rows where the country is Canada.SELECT X.NAME

55、, X.CITY, X.COUNTRYFROM XMLCUSTOMER C, XMLTable($cu/customerinfo PASSING C.INFO as cu COLUMNS NAME CHAR(20) PATH name, STREET CHAR(20) PATH addr/street, CITY CHAR(20) PATH addr/city, COUNTRY CHAR(20) PATH addr/country ) AS XWHERE X.COUNTRY = CanadaIBM Software Group 2006 IBM Corporation42XMLTable Ex

56、amples (26) Modify the previous example to output the CID, name, city and country columns4 Select only the rows where the country is Canada.4 Hint: CID isnt in the XML documentSELECT C.CID, X.NAME, X.CITY, X.COUNTRYFROM XMLCUSTOMER C, XMLTable($cu/customerinfo PASSING C.INFO as cu COLUMNS NAME CHAR(

57、20) PATH name, STREET CHAR(20) PATH addr/street, CITY CHAR(20) PATH addr/city, COUNTRY CHAR(20) PATH addr/country ) AS XWHERE X.COUNTRY = CanadaIBM Software Group 2006 IBM Corporation43XMLTable Examples (27) Modify the previous example to: 4 output the cid, name, city and country columns4 Select only the rows where the country is Canada and the CID is larger than 1001SELECT C.CID, X.NAME, X.CITY, X.COUNTRYFROM XMLCUSTOMER C, XMLTable($cu