1 / 36

Pure XML Pertemuan 10

Pure XML Pertemuan 10. Matakuliah : T0413 Tahun : 2009. XML: The foundation of SOA and Web 2.0. SOA. Web 2.0. XML. Database  DB2 9. <employee id=“ 9 ”> <name> John </name> <phone> 555 1212 </phone> </employee>. XQuery. SQL. id. name. phone. 9. John. 5551212.

destiny-hawkins
Download Presentation

Pure XML Pertemuan 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Pure XMLPertemuan 10 Matakuliah : T0413 Tahun : 2009

  2. XML: The foundation of SOA and Web 2.0 SOA Web 2.0 XML Database  DB2 9

  3. <employee id=“9”> <name>John</name> <phone> 555 1212</phone> </employee> XQuery SQL id name phone 9 John 5551212 employee XML Engine Relational Engine id=901 name phone John 555-1212 What is DB2’s pureXML technology?

  4. What is DB2’s pureXML technology? • Two main characteristics: • XML is stored in parsed-hierarchical format in the database • DB2 has a native XML interface to the engine • The storage format = the processing format

  5. Native XML Storage • XML stored in parsed hierarchical format create table dept (deptID char(8),…, deptdoc xml); • Relational columnsare stored in relationalformat (tables) • XML columns arestored natively • XML stored in UTF8

  6. Integration: Relational and hierarchical data PATIENTS table geneX = 987 geneX = 987 geneX = 123

  7. SQL with Xquery/XPath SELECT name from PATIENTS WHERE xmlexists('$p/Patient/MedicalDetail[geneX="987"]' passing PATIENTS.DNA as "p") and sex = 'F' and age > 55 and coffee > 15 and smoke > 10 ;

  8. pureXML: No need to remap for DataExchange Company Data Company Data Edge Edge Relational And XML DB Relational And XML DB Applications Applications XML Processing XML Processing • XML Exchange Formats: • Web Services • Syndication • Industry Formats • Storing and querying XML without needing to re-map exchange data to relational format • Providing common integrity, recovery, security, management interfaces and mechanisms • Providing flexibility in the face of schema evolution • Supporting easy access through Web Services and Web 2.0

  9. Table definitions with XML columns create table items ( id int primary key not null, brandname varchar(30), itemname varchar(30), sku int, srp decimal(7,2), comments xml ); create table clients( id int primary key not null, name varchar(50), status varchar(10), contact xml );

  10. Insert & Import of XML data INSERT INTO clients VALUES (77, 'John Smith', 'Gold', '<addr>111 Main St., Dallas, TX, 00112</addr>') ; IMPORT from "C:\DB2workshop\Quicklabs\quicklab14a\clients.del" of del xml from "C:\DB2workshop\Quicklabs\quicklab14a" INSERT INTO CLIENTS (ID, NAME, STATUS, CONTACT); IMPORT from "C:\DB2workshop\Quicklabs\quicklab14a\items.del" of del xml from "C:\DB2workshop\Quicklabs\quicklab14a" INSERT INTO ITEMS (ID, BRANDNAME, ITEMNAME, SKU, SRP, COMMENTS);

  11. / /dept /dept/employee /dept/employee/@id /dept/employee/name dept /dept/employee/phone /dept/employee/office (...) employee employee id=901 name phone office id=902 name phone office John Doe 408-555-1212 344 Peter Pan 408-555-9918 216 XPath • XML Query Language • Subset of XQuery & SQL/XML • <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept> Each node has a path

  12. <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept> XPath: Simple XPath Expressions • Use fully qualified paths to specify elements/attributes • “@” is used to specify an attribute • use “text()” to specify the text node under an element

  13. <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept> XPath: Wildcards • * matches any tag name • // is the “descendent-or-self” wildcard

  14. XPath:Predicates • <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept> • Predicates are enclosed in square brackets […] • Can have multiple predicates in one XPath • Positional predicates: [n] selects the n-th child

  15. <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept> XPath: The Parent Axis • Current context: “.” • Parent context: “..”

  16. XQuery • XQuery supports path expressions to navigate XML hierarchical structure • XQuery supports both typed and untyped data • XQuery lacks null values because XML documents omit missing or unknown data • XQuery returns sequences of XML data

  17. XQuery: The FLWOR Expression • FOR: iterates through a sequence, bind variable to items • LET: binds a variable to a sequence • WHERE: eliminates items of the iteration • ORDER: reorders items of the iteration • RETURN: constructs query results Input: • create table dept(deptID char(8),deptdoc xml); • xquery • for $d in db2-fn:xmlcolumn(‘DEPT.DEPTDOC')/dept • let $emp := $d//employee/name • where $d/@bldg > 95 • order by $d/@bldg • return • <EmpList> • {$d/@bldg, $emp} • </EmpList> • <dept bldg=“101”> • <employee id=“901”> • <name>John Doe</name> • <phone>408 555 1212</phone> • <office>344</office> • </employee> • <employee id=“902”> • <name>Peter Pan</name> • <phone>408 555 9918</phone> • <office>216</office> • </employee> • </dept>

  18. SQL/XML queries • SQL/XML is designed to bridge between the SQL and XML worlds. • Part of the SQL standard includes specifications for embedding XQuery or XPath expressions within SQL statements • XPATH is a language for navigating XML documents to find elements or attributes • XQuery includes support for XPATH xmlexists function • Restricts results based on an XML element value • Syntax required prior to DB2 9.5: • New syntax allowed with DB2 9.5: select name from clients where xmlexists('$c/Client/Address[zip="95116"]' passing CLIENTS.CONTACT as "c") select name from clients where xmlexists('$CONTACT/Client/Address[zip="95116"]')

  19. Case sensitivity - Caution! SQL is not case sensitiveXPath and XQuery are case sensitive!DB2 objects (tables/columns) need to be put in upper case.Example: select name from clients where xmlexists('$contact/Client/Address[zip="95116"]') select name from clients where xmlexists('$CONTACT/Client/Address[zip="95116"]')

  20. Curly quotes - Caution! SQL, XPath and XQuery use straight quotes (for either single or double quotes depending the situation)Examples: ‘The quotes in this example are curly, which is bad!’ 'The quotes in this example are straight, which is good!'The example below uses curly quotes, so it will fail:It is typical to see this problem when copy/pasting from a Word or Powerpoint document xquery db2-fn:xmlcolumn(‘CLIENTS.CONTACT’)/Client/Address[zip=“95116”]

  21. xmlquery function • Retrieve one or more element values from our XML document select xmlquery('$CONTACT/Client/email') from clients where status = 'Gold' Retrieving XML data using “for” and “return” clauses of XQuery SELECT name, xmlquery ('for $e in $CONTACT/Client/email[1] return $e') FROM clients WHERE status = 'Gold'

  22. Retrieving and transforming XML into HTML SELECT xmlquery('for $e in $CONTACT/Client/email[1]/text() return <p>{$e}</p>') FROM clients WHERE status = 'Gold' xmltable function: From XML to Relational select t.comment#,i.itemname,t.customerID,Message from items i, xmltable('$COMMENTS/Comments/Comment' columns Comment# integer path 'CommentID', CustomerID integer path 'CustomerID', Message varchar(100) path 'Message') as t

  23. XML XMLELEMENT function XMLTABLE function Relational xmlelement function: From Relational to XML select xmlelement (name "item",itemname), xmlelement (name "id", id), xmlelement (name "brand",brandname), xmlelement (name "sku",sku) from items where srp < 100 Review: XMLTABLE vs XMLELEMENT

  24. Simple XQuery to return customer contact data xquery db2-fn:xmlcolumn('CLIENTS.CONTACT') • db2-fn:xmlcolumn is a function with a parameter that identifies the table name and column name of an XML column. FLWOR expression to retrieve client fax data xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACT')/Client/fax return $y <fax>4081112222</fax> <fax>5559998888</fax>

  25. Path expression with additional filtering predicate xquery db2-fn:xmlcolumn('CLIENTS.CONTACT')/Client/Address[zip="95116"] Querying DB2 XML data and returning results as HTML xquery <ul> { for $y in db2-fn:xmlcolumn('CLIENTS.CONTACT')/Client/Address order by $y/zip return <li>{$y}</li> }</ul>

  26. Sample HTML <ul> <li> <address> <street>9407 Los Gatos Blvd.</street> <city>Los Gatos</city> <state>ca</state> <zip>95302</zip> </address> </li> <Address> <street>4209 El Camino Real</street> <city>Mountain View</city> <state>CA</state> <zip>95302</zip> </address> </li> … </ul>

  27. Embedded SQL within XQuery • db2-fn:sqlquery • A function which executes an SQL query and returns only the selected data • The SQL Query passed to db2-fn:sqlquery must return XML data • This XML data can then be further processed by XQuery xquery for $y in db2-fn:sqlquery('select comments from items where srp > 100')/Comments/Comment where $y/ResponseRequested='Yes' return ( <action> {$y//ProductID} {$y//CustomerID} {$y//Message} </action> )

  28. Joins with SQL/XML create table dept (unitID char(8), deptdoc xml) create table unit (unitID char(8) primary key not null, name char(20), manager varchar(20), ... ) select u.unitID from dept d, unit u where XMLEXISTS ('$e//employee[name = $m] ' passing d.deptdoc as "e", u.manager as "m") select u.unitID from dept d, unit u where u.manager = XMLCAST(XMLQUERY('$e//employee/name' passing d.deptdoc as "e") as char(20))

  29. DELETE operations • DELETE a row based on a condition that uses an XML element • Simply use an SQL DELETE statements • A DELETE first searches for the document, and then deletes it. The search part can be done using the same SQL/XML functions as when querying data UPDATE operations • With DB2 9, use: • SQL UPDATE statement • or Stored Procedure • DB2XMLFUNCTIONS.XMLUPDATE • With DB2 9.5: • Use the TRANSFORM expression

  30. Update Example with DB2 9.5: TRANSFORM expression Adding an element to the end UPDATE purchaseorder SET porder = xmlquery('transform copy $po := $order modify do insert document { <item> <partid>100-103-01</partid> <name>Snow Shovel, Super Deluxe 26 inch</name> <quantity>4</quantity> <price>49.99</price> </item> } into $po/PurchaseOrder return $po' passing purchaseorder.porder as "order") where poid=5012;

  31. Update Example with DB2 9.5: TRANSFORM expression Deleting an element UPDATE purchaseorder SET porder = xmlquery('transform copy $po := $order modify do delete $po/PurchaseOrder/item[partid = ''100-201-01''] return $po' passing porder as "order") WHERE poid=5012;

  32. XML Indexing Examples create unique index idx1 on customer(info) generate key using xmlpattern '/customerinfo/@Cid' as sql double; create index idx2 on customer(info) generate key using xmlpattern '/customerinfo/name' as sql varchar(40); create tablecustomer(infoXML); <customerinfo Cid="1004"> <name>Matt Foreman</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Toronto</city> <state>Ontario</state> <pcode>M3Z-5H9</pcode> </addr> <phone type="work">905-555-4789</phone> <phone type="home">416-555-3376</phone> <assistant> <name>Peter Smith</name> <phone type="home">416-555-3426</phone> </assistant> </customerinfo> create indexidx3oncustomer(info) generate key using xmlpattern '//name' as sql varchar(40);

  33. <customerinfo Cid="1004"> <name>Matt Foreman</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Toronto</city> <state>Ontario</state> <pcode>M3Z-5H9</pcode> </addr> <phone type="work">905-555-4789</phone> <phone type="home">416-555-3376</phone> <assistant> <name>Peter Smith</name> <phone type="home">416-555-3426</phone> </assistant> </customerinfo> create indexidx4oncustomer(info) generate key using xmlpattern '//text()' as sql varchar(40); XML Indexing Examples create tablecustomer(infoXML); Don’t index everything! Too expensive for insert, update, delete !

  34. XML Schema Support • XML Schemas are supported using “XML Schema repositories” • To validate based on an XML Schema you can: • Use the XMLVALIDATE function during an INSERT • Use a BEFORE Trigger • To test if an XML document has been validated, you can use the “IS VALIDATED” predicate on a CHECK constraint

  35. What does an XML Schema do? DB2 pureXML Schema Flexibility Document validation for zero, one, or many schemas per XML column: Always Well Formed XML (a) (b) (c) (d) (e) No Schema One Schema Schema V1 Documents Any mix you want! & Schema V2 w/ and w/o schema Most databases only support (a) and (b). DB2 9 allows (a) through (e).

More Related