XML TO RALATIONAL : BRIDGING THE GAP Storing XML in traditional storage

Tips dan Trik IlmuKomputer.Com
Copyright © 2003 - 2006 IlmuKomputer.Com


Fajar Tjahyono
fajartjahyono@yahoo.com
Lisensi Dokumen:
Copyright © 2003- 2006 IlmuKomputer.Com
Seluruh dokumen di IlmuKomputer.Com dapat digunakan, dimodifikasi dan disebarkan secara
bebas untuk tujuan bukan komersial (nonprofit), dengan syarat tidak menghapus atau merubah
atribut penulis dan pernyataan copyright yang disertakan dalam setiap dokumen. Tidak
diperbolehkan melakukan penulisan ulang, kecuali mendapatkan ijin terlebih dahulu dari
IlmuKomputer.Com.
INTRODUCING DBMS_ XMLSTORE
The DBMS_XMLSTRORE PL/SQL package was introduced in Oracle Database !)g
Release 1. This package performs DML operations on relational or object tables inside
the Database, based on the contents of an XML document.
Note that before Oracle Database 10g, this functionality existed in another
PL/SQL apackage, called DBMS_ XMLSAVE.
CANONICAL XML
In order to use DBMS_XMLSTORE, you need to format your XML documents in
Oracle’s canonical XML format. “this format is very straigthforward; each element in the
XML document will map to a column, and the element name will be the column name.
Elements that make up a row in the XML document aare placed under a
element, and all of the elements are placed inside a element. If you
take two rows from the standard EMP table and represent them in Oracle canonical
format, you get the following XML document:


7499
ALLEN
SALESMAN
7698
20-FEB-81
1600/SAL>
300/COMM>
DEPTNO>30


7521
WARD
1

Tips dan Trik IlmuKomputer.Com
Copyright © 2003 - 2006 IlmuKomputer.Com
,JOB>SALESMAN
7698
22-FEB-81
1250
500
30


EMPLOYING THE DBMS_XMLSTORE PACKAGE
You can perform the basic INSERT, and UPDATE operation with
DBMS_XMLSTORE. The DBMS_XMLSTORE package also offers a variety of
procedures for customizing these operations. Let’s walk through and discuss how to use
each of the operations available.
Inserts. DBMS_XMLSTORE is the function used to insert rows into the database. In
this first example, you create a new table that resembles the EMP table called
SALES_EMP :
SQL>create table sales_emp
2 as select * from emp where 1=0
3/
table created.
Now you create an XML document that contains all of the employees from the
SALES department and insert these employees into the new SALES_EMP table. Lines
7 through 18 of listing 1 create the XML document from the EMP and DEPT tables.
In the anonymous OL/SQL block in listing 1, you can see calls to the
DBMS_XMLSTORE package to accomplish this insert. Line 21 uses NEWCONTEXT( )
to create a new context using the new table name (SALES_EMP). Line 22 calls the
INSERTXML function, which by default inserts data into every column of the table and
returns the number of rows inserted. Finally, output that information using the
DBMS_OUTPUTpackage (line 26) and clean up the context in the DBMS_XMLSTORE
package using the CLOSECONTEXT( ) procedure (line 29).
The following query shows the six rows inserted in SALES_EMP by listing 1 :
SQL>select empno, ename, job, sal
2from sales_emp;
EMPNO ENAMEJOBSAL
_______ ________ __________ _________
7499
7521
7654
7698
7644
7900
ALLENSALESMAN 1600
WARDSALESMAN 1250
MARTIN SALESMAN 1250
BLAKESALESMAN 2850
TURNER SALESMAN 1500
JAMESCLERK950
2

Tips dan Trik IlmuKomputer.Com
Copyright © 2003 - 2006 IlmuKomputer.Com
code LISTING 1 : INSERT using DBMS_XMLSTORE
SQL>declare
2l_sales_emp xmltype’
3l_ctxdbms_xmlstore.ctxtype;
4l_rowspls_integer;
5 begin
6- -get all the sales employees into an xml document
7select xmlelement(“ROWSET”,
8xmlagg(
10xmlelement(“ROW”,
11xmlforest(e.empno,e.ename,e.job,e.mgr,
12e.hiredate,e.sal, e.comm.,e.deotno)
13)
14)
15)
16into l_sales_emp
17from emp e, dept d
18where d.deptno = e.deptno
19and d.dname=’SALES’;
20
21- - setup our dbms_xmlstore context
22l_ctx :=dbms_xmlstore.newcontext(‘SALES_EMP’);
23l_rows := dbms_xmlstore.insertxml(
24L_ctx, l_sales_emp.getClobVal( ));
25
26- -how many rows were inserted?
27Dbms_output.put_line(l_rows ⎥⎥ ‘rows inserted into SALES_EMP’);
28
29- - clean up
30dbms_xmlstore.closecontext(l_ctx);
30 end;
31 /
6 rows inserted into SALES_EMP.
PL/SQL procedure successfully comleted
Updates. Using the example, update a couple of the SALES_EMP records to indocate
those employees who have received promotions and raises. Here is the XML, that will be
used to perform the update :


7499
ALLEN
MANAGER
7698
2600


7521
ENAME>WARD
MANAGER
7698
2250
3

Tips dan Trik IlmuKomputer.Com
Copyright © 2003 - 2006 IlmuKomputer.Com


When performing uodates using DBMS_XMLSTORE, you need to use other
procedures in the package to help the database understand what columns you are
updating and what you are using for the update key(s). Listing 2 shows the process for
updating the two rows listed above (ALLEN and WARD ).
code LISTING 2 : UPDATE using DBMS_XMLSTORE
SQL>declare
2l_sales_emp xmltype’
3l_ctxdbms_xmlstore.ctxtype;
4l_rowspls_integer;
5 begin
6- -simulate the updates to make
7l_sales_emp :=xmltype(‘
8
9
107499ALLENMANAGER
76982600
11
12
137521WARDMANAGER
76982250
14
15’);
16
17- - setup our dbms_xmlstore context
18l_ctx :=dbms_xmlstore.newcontext(‘SALES_EMP’):
19A
20- -setup the columns to be upated
21dbms_xmlstore.clearupdatecolumnlist(l_ctx);
22dbms_xmlstore.setupdatecolumn(l_ctx, ‘ENAME’);
23dbms_xmlstore.setupdatecolumn(l_ctx, ‘JOB’);
24dbms_xmlstore.setupdatecolumn(l_ctx, ‘MGR’);
25dbms_xmlstore.setupdatecolumn(l_ctx, ‘SAL’);
26
27- -setup the key columns to update by
28dbms_xmlstorer.setkeycolumn(l_ctx, ‘empno’);
29
30- - perform the update
31l_rows := dbms_xmlstore.updatexml(l_ctx, l_sales_emp.getClobVal( ));
32
33- -how many rows were updated?
34Dbms_output.put_line(l_rows ⎥⎥ ‘rows updated into SALES_EMP’);
35
36- - clean up
37dbms_xmlstore.closecontext(l_ctx);
38 end;
39 /
2 rows updated into SALES_EMP.
PL/SQL procedure successfully comleted
4

Tips dan Trik IlmuKomputer.Com
Copyright © 2003 - 2006 IlmuKomputer.Com
STORING XML DATA RELATIONALLY
DBMS_XMLSTORE offers one way to take the content from XML documents and store
it in (and remove it from) relational tables. (If you are not yet using Oracle Database 10g,
you might try DBMS_XML to do the same).