Tutorial 10 - IBMDB2

Pure XML - SQL/XML & XQuery


XML : Penemuan SOA dan Web 2.0
Service Oriented Architecture (SOA) which is based on Web services, and Web 2.0 technologies are becoming more and more important today.  Both of these technologies use XML as their foundation. 
Now, where are we going to store XML documents?  They need to be stored somewhere right?
- Maybe in files  (Bad in terms of security, performance, and also if you want to parse or retrieve part of the XML document)
- Maybe in XML repositories (special software just to store XML.  Eg: Tamino --> Why re-invent the wheel?  We have databases that would do the same as this already!)

Apa itu Teknologi pureXML DB2?
  • 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

Penyimpanan XML Asing
  • XML stored in parsed hierarchical format
            create table dept (deptID char(8),…, deptdoc xml);

  • Relational columns
    are stored in relational
    format (tables)
  • XML columns are
    stored natively
  • XML stored in UTF8

SQL dengan 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
;

PureXML
Ø  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

Definisi Tabel dengan Kolom XML
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
);

Insert dan Import data XML
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);

Xpath
XPath is a query language used to navigate an XML document. A Path expression in XPath consists of a series of “steps” separated by slash characters.
 It is fairly easy to learn.  Just compare it to the “cd” (change directory) command in MS-DOS/Linux/UNIX.  In the “cd” command you are navigating a tree which is hierarchical in nature.  XML is also hierarchical. Contoh :
<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>
Ekspresi Xpath:
n  Use fully qualified paths to specify elements/attributes
n  “@” is used to specify an attribute
n   use “text()” to specify the text node under an element
Wildcards:
n  * matches any tag name
n  // is the “descendent-or-self” wildcard
Predikat :
n  Predicates are enclosed in square brackets […]
n  Can have multiple predicates in one XPath
n  Positional predicates: [n] selects the n-th child
The Parent Axis
n  Current context:   “.”
n  Parent context:   “..”

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

 Exspresi FLWOR
  • 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

Apa yang bisa anda lakukan dengan dan apa yang tidak bisa anda lakukan dengan SQL
  • Plain SQL statements enable you to retrieve full XML documents but:
-  You cannot specify XML-based query predicates
-  You cannot retrieve partial XML documents 
-  You cannot join, aggregate or order by fragments of XML documents using plain SQL

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

Fungsi SQL/XML di SQL 2006
  • XMLPARSE
-  parses character/BLOB data, produces XML value
  • XMLSERIALIZE
-  converts an XML value into character/BLOB data
  • XMLVALIDATE
-  validates XML value against an XML schema and type-annotates the XML value
  • XMLEXISTS
-  determines if an XQuery returns a results (i.e. a sequence of one or more items)
  • XMLQUERY
-  executes an XQuery and returns the result sequence
  • XMLTABLE
-  executes an XQuery, returns the result sequence as a relational table (if possible)
  • XMLCAST
-  cast to or from an XML type

Fungsi xmlexists :Restricts results based on an XML element value
SQL is not case sensitive
XPath and XQuery are case sensitive!
DB2 objects (tables/columns) need to be put in upper case.
SQL, XPath and XQuery use straight quotes (for either single or double quotes depending the situation)
Fungsi xmlquery : Retrieve one or more element values from our XML document

 Embedded SQL dalam 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

Join dengan 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")

Operasi Delete

§  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

Operasi Delete : Di DB2 9.5, gunakan ekspresi transform
§  Many other things you can do with TRANSFORM:
- Replace the value of an element
- Replace the value of an attribute
- Replace an element and attribute
- Rename an element anD attribute

Contoh Pengindeksan XML
create table customer( info XML);
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 index idx3 on customer(info)
generate key using
xmlpattern '//name'
as sql varchar(40);

Pendukung XML Schema
§  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

Support XML yang lain
§  Based-table inlining and compression of small XML documents
§  Can transform XML documents using XSLT functions
§  Compatible XML Schema evolution using the UPDATE XMLSCHEMA command
§  pureXML supported for UNICODE or non-UNICODE dbs with DB2 9.5
§  Annotated XML Schema Decomposition 

Comments

Post a Comment