XML and ORACLE Rosa Isela Lpez Aguilar Noviembre

XML and ORACLE Rosa Isela López Aguilar Noviembre 2008

Oracle XML DB Oracle Database supports native XML generation. Oracle provides you with several options for generating or regenerating XML data when stored in: Oracle Database, in general Oracle Database in XMLTypes columns and tables

Creating a Table with an XMLType Column CREATE TABLE Ejemplo 1(KEYVALUE varchar 2(10) primary key, XMLCOLUMN xmltype); select * from Ejemplo 1; Creating a Table of XMLType CREATE TABLE XMLTABLE OF XMLType; select * from XMLTABLE;

Inserting Values on Ejemplo 1 INSERT INTO ejemplo 1 VALUES(100, XMLType('<Warehouse wh. No="100"><Building>Owned</Building></Warehouse>')); select * from ejemplo 1;

Inserting Values on Ejemplo 1 INSERT INTO ejemplo 1 VALUES(101, XMLType('<Purchase. Order xmlns: xsi="http: //www. w 3. org/2001/XMLSchema-instance" xsi: no. Namespace. Schema. Location="http: //www. oracle. com/xdb/po. xsd"> <Reference>ADAMS-20011127121040988 PST</Reference> <Actions> <Action> <User>SCOTT</User> <Date>2002 -03 -31</Date> </Actions> <Reject/> <Requestor>Julie P. Adams</Requestor> <User>ADAMS</User> <Cost. Center>R 20</Cost. Center> <Shipping. Instructions> <name>Julie P. Adams</name> <address>Redwood Shores, CA 94065</address> <telephone>650 506 7300</telephone> </Shipping. Instructions> <Special. Instructions>Ground</Special. Instructions> <Line. Item. Number="1"> <Description>The Ruling Class</Description> <Part Id="715515012423" Unit. Price="39. 95" Quantity="2"/> </Line. Item> <Line. Item. Number="2"> <Description>Diabolique</Description> <Part Id="037429135020" Unit. Price="29. 95" Quantity="3"/> </Line. Item> <Line. Item. Number="3"> <Description>8 1/2</Description> <Part Id="037429135624" Unit. Price="39. 95" Quantity="4"/> </Line. Items> </Purchase. Order> ')); select * from ejemplo 1;

exists. Node() Examples That Find a Node to Match the XPath Expression Given this sample XML document, the following exists. Node() operators return true (1). SELECT exists. Node(XMLCOLUMN, '/Purchase. Order/Reference') FROM ejemplo 1; SELECT exists. Node(XMLCOLUMN, '/Purchase. Order[Reference="ADAMS 20011127121040988 PST"]') FROM ejemplo 1; SELECT exists. Node(XMLCOLUMN, '/Purchase. Order/Line. Items/Line. Item[2]/Part[@Id="037429135020"]') FROM ejemplo 1; SELECT exists. Node(XMLCOLUMN, '/Purchase. Order/Line. Items/Line. Item[Description="8 1/2"]') FROM ejemplo 1;

Overview of Generating XML Using Standard SQL/XML Functions You can generate XML data using any of the following standard SQL/XML functions supported by Oracle XML DB: XMLELEMENT and XMLATTRIBUTES SQL Functions XMLFOREST SQL Function XMLCONCAT SQL Function XMLAGG SQL Function XMLPI SQL Function XMLCOMMENT SQL Function XMLROOT SQL Function XMLSERIALIZE SQL Function XMLPARSE SQL Function

XMLELEMENT SELECT XMLCOLUMN, XMLELEMENT("emp", KEYVALUE) AS "result" FROM ejemplo 1 WHERE KEYVALUE > 100; XMLElement(): Generating Nested XML SELECT XMLELEMENT("Emp", XMLELEMENT("name", XMLCOLUMN), XMLELEMENT ( "emp", KEYVALUE)) AS "result" FROM ejemplo 1 WHERE KEYVALUE > 100 ; XMLElement(): Generating an Element for Each Employee with ID Attribute SELECT XMLELEMENT("Emp", XMLATTRIBUTES(KEYVALUE AS "ID") ) AS "result" FROM ejemplo 1 WHERE KEYVALUE > 100;

XML EXTRACT SELECT extract(XMLCOLUMN, '/emp//enumber') FROM ejemplo 1; Using exists. Node() in the WHERE Clause SELECT count(*) FROM ejemplo 1 WHERE exists. Node(XMLCOLUMN, '/Purchase. Order[User="ADAM S"]') = 1; Using delete in the WHERE Clause DELETE FROM ejemplo 1 WHERE exists. Node(XMLCOLUMN, '/Purchase. Order[User="ADAM S"]') = 1;

XMLELEMENT: Generating an Element for Each Employee This example produces an Emp element for each employee, with the employee name as its content: SELECT e. employee_id, XMLELEMENT ("Emp", e. first_name ||' '|| e. last_name) AS "RESULT" FROM hr. employees e WHERE employee_id > 200; This query produces the following typical result: EMPLOYEE_ID RESULT -----------------------201 <Emp>Michael Hartstein</Emp> 202 <Emp>Pat Fay</Emp> 203 <Emp>Susan Mavris</Emp> 204 <Emp>Hermann Baer</Emp> 205 <Emp>Shelley Higgins</Emp> 206 <Emp>William Gietz</Emp>

XMLELEMENT: Generating an Element for a Particular Case SELECT KEYVALUE, XMLELEMENT("NUEVO", KEYVALUE||' '||XMLCOLUMN) AS "RESULT" FROM ejemplo 2 WHERE KEYVALUE=101;

XMLELEMENT: Generating Nested XML To produce an Emp element for each employee, with elements that provide the employee name and hire date, do the following: SELECT XMLElement("Emp", XMLElement("name", e. first_name ||' '|| e. last_name), XMLElement("hiredate", e. hire_date)) AS "RESULT" FROM hr. employees e WHERE employee_id > 200 ; RESULT -----------------------------------<Emp><name>Michael Hartstein</name><hiredate>1996 -0217</hiredate></Emp> <Emp><name>Pat Fay</name><hiredate>1997 -0817</hiredate></Emp> <Emp><name>Susan Mavris</name><hiredate>1994 -0607</hiredate></Emp> <Emp><name>Hermann Baer</name><hiredate>1994 -0607</hiredate></Emp>

XMLELEMENT: Generating Employee Elements with ID and Name Attributes This example produces an Emp element for each employee, with an id and name attribute: SELECT XMLElement("Emp", XMLAttributes( e. employee_id as "ID", e. first_name ||' ' || e. last_name AS "name")) AS "RESULT" FROM hr. employees e WHERE employee_id > 200; RESULT -----------------------<Emp ID="201" name="Michael Hartstein"></Emp> <Emp ID="202" name="Pat Fay"></Emp> <Emp ID="203" name="Susan Mavris"></Emp> <Emp ID="204" name="Hermann Baer"></Emp> <Emp ID="205" name="Shelley Higgins"></Emp> <Emp ID="206" name="William Gietz"></Emp>

XMLELEMENT: Generating an Element from a User-Defined Datatype Instance CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4), ENAME VARCHAR 2(10)); CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t; CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2), DNAME VARCHAR 2(14), EMP_LIST emplist_t); SELECT XMLElement("Department", dept_t(department_id, department_name, CAST(MULTISET(SELECT employee_id, last_name FROM hr. employees e WHERE e. department_id = d. department_id) AS emplist_t))) AS deptxml FROM hr. departments d WHERE d. department_id = 10;

This produces an XML document which contains the Department element and the canonical mapping of type dept_t. DEPTXML ------<Department> <DEPT_T DEPTNO="10"> <DNAME>ACCOUNTING</DNAME> <EMPLIST> <EMP_T EMPNO="7782"> <ENAME>CLARK</ENAME> </EMP_T> <EMP_T EMPNO="7839"> <ENAME>KING</ENAME> </EMP_T> <EMP_T EMPNO="7934"> <ENAME>MILLER</ENAME> </EMP_T> </EMPLIST> </DEPT_T> </Department>

Accessing a Text Node Value Matching an XPath Expression Using extract. Value() SELECT extract. Value(XMLCOLUMN, '/Purchase. Order/Reference') FROM ejemplo 1; SELECT extract. Value(XMLCOLUMN, '/Description') FROM ejemplo 1, TABLE ( xmlsequence (extract(XMLCOLUMN, '/Purchase. Order/Line. Items/Line. Item/Descripti on') ) ) t;

Using update. XML() to Replace Contents of a Node Tree Associated with XPath Elements In this example update. XML() replaces the contents of the node tree associated with the element identified by the XPath expression `/Purchase. Orders/Line. Item[2]'. UPDATE ejemplo 1 SET xmlcolumn = update. XML(xmlcolumn, '/Purchase. Order/Line. Items/Line. Item[2]', xmltype('<Line. Item. Number="4"> <Description>Andrei Rublev</Description> <Part Id="715515009928" Unit. Price="39. 95" Quantity="2"/> </Line. Item>' ) ) WHERE exists. Node(XMLCOLUMN, '/Purchase. Order[Reference="MILLER 20020331120000 PST"]' ) = 1; SELECT * FROM ejemplo 1;

Using update. XML() to Update a Text Node Value Identified by an XPath Expression This example uses update. XML() to update the value of the text node identified by the XPath expression `/Purchase. Order/Reference': UPDATE ejemplo 1 SET XMLCOLUMN = update. XML(XMLCOLUMN, '/Purchase. Order/Reference/text()', 'MILLER-20020331120000 PST') WHERE exists. Node(XMLCOLUMN, '/Purchase. Order[Reference="ADAMS 20011127121040988 PST"]') = 1; SELECT * FROM ejemplo 1;

XMLFOREST: Generating Elements with Attribute and Child Elements This example generates an Emp element for each employee, with a name attribute and elements with the employee hire date and department as the content. SELECT XMLElement("Emp", XMLAttributes(e. first_name ||' '|| e. last_name AS "name"), XMLForest(e. hire_date, e. department AS "department")) AS "RESULT" FROM employees e WHERE e. department_id = 20; RESULT ------------------<Emp name="Michael Hartstein"> <HIRE_DATE>1996 -02 -17</HIRE_DATE> <department>20</department> </Emp> <Emp name="Pat Fay"> <HIRE_DATE>1997 -08 -17</HIRE_DATE> <department>20</department> </Emp>

XMLFOREST: Generating an Element from a User-Defined Datatype Instance SELECT XMLForest( dept_t(department_id, department_name, CAST (MULTISET (SELECT employee_id, last_name FROM hr. employees e WHERE e. department_id = d. department_id) AS emplist_t)) AS "Department") AS deptxml FROM hr. departments d WHERE department_id=10; DEPTXML ----------------<Department DEPTNO="10"> <DNAME>Administration</DNAME> <EMP_LIST> <EMP_T EMPNO="200"> <ENAME>Whalen</ENAME> </EMP_T> </EMP_LIST> </Department>

XMLFOREST SELECT XMLELEMENT("Emp", XMLFOREST(KEYVALUE, XMLCOLUMN)) "Emp Element" FROM ejemplo 1 WHERE KEYVALUE > 99;

XMLTRANSFORM CREATE TABLE datosxml(Colxml XMLType); CREATE TABLE xsl_tab (col 1 XMLTYPE); Insert Into datosxml Values ( xmltype('<empleado> <nombre>Juan</nombre> <apellido>garcia</apellido> </empleado>')); SELECT * FROM datosxml;

XMLTRANSFORM Inserting Style. Sheet: INSERT INTO xsl_tab VALUES (XMLTYPE. createxml('<? xml version="1. 0"? > <xsl: stylesheet version="1. 0" xmlns: xsl="http: //www. w 3. org/1999/XSL/Transform" > <xsl: output encoding="utf-8"/> <!-- alphabetizes an xml tree --> <xsl: template match="*"> <xsl: copy> <xsl: apply-templates select="*|text()"> <xsl: sort select="name(. )" data-type="text" order="ascending"/> </xsl: apply-templates> </xsl: copy> </xsl: template> </xsl: stylesheet>'));

Result XMLTRANSFORM SELECT XMLTRANSFORM(d. colxml, x. col 1) FROM datosxml d, xsl_tab x;

Referencias http: //lbd. epfl. ch/f/teaching/courses/oracle 9 i/appdev. 920/a 966 20/xdb 03 usg. htm#1656 http: //www. acs. ilstu. edu/docs/oracle/server. 101/b 10759/funct ions 204. htm