XML and ORACLE

1
XML and ORACLE
Rosa Isela López Aguilar
Noviembre 2008
2
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

3
Creating a Table with an XMLType Column
CREATE TABLE Ejemplo1(KEYVALUE varchar2(10)
primary key, XMLCOLUMN xmltype) select from
Ejemplo1
Creating a Table of XMLType
CREATE TABLE XMLTABLE OF XMLType select from
XMLTABLE
4
Inserting Values on Ejemplo1
INSERT INTO ejemplo1 VALUES(100,
XMLType('ltWarehouse whNo"100"gtltBuildinggtOwnedlt/Bu
ildinggtlt/Warehousegt')) select from ejemplo1
5
Inserting Values on Ejemplo1
INSERT INTO ejemplo1 VALUES(101,
XMLType('ltPurchaseOrder xmlnsxsi"http//www.w3
.org/2001/XMLSchema-instance"
xsinoNamespaceSchemaLocation"http//www.oracle.c
om/xdb/po.xsd"gt ltReferencegtADAMS-200111271210409
88PSTlt/Referencegt ltActionsgt ltActiongt
ltUsergtSCOTTlt/Usergt ltDategt2002-03-31lt/Dategt
lt/Actiongt lt/Actionsgt ltReject/gt
ltRequestorgtJulie P. Adamslt/Requestorgt
ltUsergtADAMSlt/Usergt ltCostCentergtR20lt/CostCentergt
ltShippingInstructionsgt ltnamegtJulie P.
Adamslt/namegt ltaddressgtRedwood Shores, CA
94065lt/addressgt lttelephonegt650 506
7300lt/telephonegt lt/ShippingInstructionsgt
ltSpecialInstructionsgtGroundlt/SpecialInstructionsgt
ltLineItemsgt ltLineItem ItemNumber"1"gt
ltDescriptiongtThe Ruling Classlt/Descriptiongt
ltPart Id"715515012423" UnitPrice"39.95"
Quantity"2"/gt lt/LineItemgt ltLineItem
ItemNumber"2"gt ltDescriptiongtDiaboliquelt/Des
criptiongt ltPart Id"037429135020"
UnitPrice"29.95" Quantity"3"/gt lt/LineItemgt
ltLineItem ItemNumber"3"gt ltDescriptiongt8
1/2lt/Descriptiongt ltPart Id"037429135624"
UnitPrice"39.95" Quantity"4"/gt lt/LineItemgt
lt/LineItemsgt lt/PurchaseOrdergt ')) select from
ejemplo1
6
existsNode() Examples That Find a Node to Match
the XPath Expression
Given this sample XML document, the following
existsNode() operators return true (1). SELECT
existsNode(XMLCOLUMN,'/PurchaseOrder/Reference')?
FROM ejemplo1 SELECT existsNode(XMLCOLUMN,'/P
urchaseOrderReference"ADAMS-20011127121040988PST
"')? FROM ejemplo1 SELECT existsNode(XMLCOLUM
N, '/PurchaseOrder/LineItems/LineItem2/Pa
rt_at_Id"037429135020"')? FROM
ejemplo1 SELECT existsNode(XMLCOLUMN,
'/PurchaseOrder/LineItems/LineItemDescription"8
1/2"')? FROM ejemplo1
7
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
8
XMLELEMENT
SELECT XMLCOLUMN, XMLELEMENT("emp", KEYVALUE) AS
"result" FROM ejemplo1 WHERE KEYVALUE gt 100
XMLElement() Generating Nested XML
SELECT XMLELEMENT("Emp", XMLELEMENT("name",
XMLCOLUMN), XMLELEMENT ( "emp", KEYVALUE)) AS
"result" FROM ejemplo1 WHERE KEYVALUE gt 100
XMLElement() Generating an Element for Each
Employee with ID Attribute
SELECT XMLELEMENT("Emp", XMLATTRIBUTES(KEYVALUE
AS "ID") ) AS "result" FROM ejemplo1 WHERE
KEYVALUE gt 100
9
XML EXTRACT
SELECT extract(XMLCOLUMN,'/emp//enumber') FROM
ejemplo1
Using existsNode() in the WHERE Clause
SELECT count() FROM ejemplo1 WHERE
existsNode(XMLCOLUMN,'/PurchaseOrderUser"ADAMS"
') 1
Using delete in the WHERE Clause
DELETE FROM ejemplo1 WHERE existsNode(XMLCOLUMN
,'/PurchaseOrderUser"ADAMS"') 1
10
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 gt 200 This query produces
the following typical result EMPLOYEE_ID
RESULT ----------- -------------------------------
---- 201 ltEmpgtMichael Hartsteinlt/Empgt
202 ltEmpgtPat Faylt/Empgt 203 ltEmpgtSusan
Mavrislt/Empgt 204 ltEmpgtHermann Baerlt/Empgt
205 ltEmpgtShelley Higginslt/Empgt 206
ltEmpgtWilliam Gietzlt/Empgt
11
XMLELEMENT Generating an Element for a
Particular Case
SELECT KEYVALUE, XMLELEMENT("NUEVO", KEYVALUE'
'XMLCOLUMN) AS "RESULT" FROM ejemplo2 WHERE
KEYVALUE101
12
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
gt 200 RESULT ---------------------------------
-------------------------------------- ltEmpgtltnamegt
Michael Hartsteinlt/namegtlthiredategt1996-02-17lt/hire
dategtlt/Empgt ltEmpgtltnamegtPat Faylt/namegtlthiredategt199
7-08-17lt/hiredategtlt/Empgt ltEmpgtltnamegtSusan
Mavrislt/namegtlthiredategt1994-06-07lt/hiredategtlt/Empgt
ltEmpgtltnamegtHermann Baerlt/namegtlthiredategt1994-06-0
7lt/hiredategtlt/Empgt
13
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 gt 200 RESULT ----------------
------------------------------- ltEmp ID"201"
name"Michael Hartstein"gtlt/Empgt ltEmp ID"202"
name"Pat Fay"gtlt/Empgt ltEmp ID"203" name"Susan
Mavris"gtlt/Empgt ltEmp ID"204" name"Hermann
Baer"gtlt/Empgt ltEmp ID"205" name"Shelley
Higgins"gtlt/Empgt ltEmp ID"206" name"William
Gietz"gtlt/Empgt
14
XMLELEMENT Generating an Element from a
User-Defined Datatype Instance
CREATE OR REPLACE TYPE emp_t AS OBJECT ("_at_EMPNO"
NUMBER(4), ENAME VARCHAR2(10)) CREATE OR
REPLACE TYPE emplist_t AS TABLE OF emp_t CREATE
OR REPLACE TYPE dept_t AS OBJECT ("_at_DEPTNO"
NUMBER(2), DNAME VARCHAR2(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
15
This produces an XML document which contains the
Department element and the canonical mapping of
type dept_t. DEPTXML ------------- ltDepartmentgt
ltDEPT_T DEPTNO"10"gt ltDNAMEgtACCOUNTINGlt/DNAME
gt ltEMPLISTgt ltEMP_T EMPNO"7782"gt
ltENAMEgtCLARKlt/ENAMEgt lt/EMP_Tgt ltEMP_T
EMPNO"7839"gt ltENAMEgtKINGlt/ENAMEgt
lt/EMP_Tgt ltEMP_T EMPNO"7934"gt
ltENAMEgtMILLERlt/ENAMEgt lt/EMP_Tgt
lt/EMPLISTgt lt/DEPT_Tgt lt/Departmentgt
16
Accessing a Text Node Value Matching an XPath
Expression Using extractValue()?
SELECT extractValue(XMLCOLUMN,'/PurchaseOrder/Refe
rence') FROM ejemplo1
SELECT extractValue(XMLCOLUMN,'/Description')?
FROM ejemplo1, TABLE ( xmlsequence
(extract(XMLCOLUMN,'/PurchaseOrder/LineItems/LineI
tem/Description')? )? ) t
17
Using updateXML() to Replace Contents of a Node
Tree Associated with XPath Elements
In this example updateXML() replaces the contents
of the node tree associated with the element
identified by the XPath expression
/PurchaseOrders/LineItems/LineItem2'.
UPDATE ejemplo1 SET xmlcolumn
updateXML(xmlcolumn,
'/PurchaseOrder/LineItems/LineItem2',
xmltype('ltLineItem ItemNumber"4"gt
ltDescriptiongtAndrei Rublevlt/Descriptiongt
ltPart Id"715515009928"
UnitPrice"39.95"
Quantity"2"/gt lt/LineItemgt'
)? ) WHERE existsNode(XMLCOLUMN,
'/PurchaseOrderReference"MILLER-2002033112
00000000PST"' ) 1 SELECT FROM ejemplo1
18
Using updateXML() to Update a Text Node Value
Identified by an XPath Expression
This example uses updateXML() to update the value
of the text node identified by the XPath
expression /PurchaseOrder/Reference' UPDATE
ejemplo1 SET XMLCOLUMN updateXML(XMLCOLUMN,'/Pur
chaseOrder/Reference/text()',
'MILLER-200203311200000000PST')? WHERE
existsNode(XMLCOLUMN,'/PurchaseOrderReference"AD
AMS-20011127121040988PST"') 1 SELECT
FROM ejemplo1
19
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 ------------------------------------- ltEmp
name"Michael Hartstein"gt ltHIRE_DATEgt1996-02-17lt
/HIRE_DATEgt ltdepartmentgt20lt/departmentgt lt/Empgt lt
Emp name"Pat Fay"gt ltHIRE_DATEgt1997-08-17lt/HIRE_
DATEgt ltdepartmentgt20lt/departmentgt lt/Empgt
20
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_id10 DEPTXML ---------------
------------------ ltDepartment DEPTNO"10"gt
ltDNAMEgtAdministrationlt/DNAMEgt ltEMP_LISTgt
ltEMP_T EMPNO"200"gt ltENAMEgtWhalenlt/ENAMEgt
lt/EMP_Tgt lt/EMP_LISTgt lt/Departmentgt
21
XMLFOREST
SELECT XMLELEMENT("Emp", XMLFOREST(KEYVALUE,
XMLCOLUMN))? "Emp Element" FROM ejemplo1
WHERE KEYVALUE gt 99
22
XMLTRANSFORM
CREATE TABLE datosxml(Colxml XMLType) CREATE
TABLE xsl_tab (col1 XMLTYPE) Insert Into
datosxml Values ( xmltype('ltempleadogt ltnombregtJuan
lt/nombregt ltapellidogtgarcialt/apellidogt lt/empleadogt'
)) SELECT FROM datosxml
23
XMLTRANSFORM
Inserting StyleSheet INSERT INTO xsl_tab VALUES
(XMLTYPE.createxml('lt?xml version"1.0"?gt ltxslsty
lesheet version"1.0" xmlnsxsl"http//www.w3.org
/1999/XSL/Transform" gt ltxsloutput
encoding"utf-8"/gt lt!-- alphabetizes an xml tree
--gt ltxsltemplate match""gt ltxslcopygt ltxslapply
-templates select"text()"gt ltxslsort
select"name(.)" data-type"text" order"ascending
"/gt lt/xslapply-templatesgt lt/xslcopygt lt/xsltempl
ategt lt/xslstylesheetgt'))
24
Result XMLTRANSFORM
SELECT XMLTRANSFORM(d.colxml, x.col1) FROM
datosxml d, xsl_tab x
25
Referencias
http//lbd.epfl.ch/f/teaching/courses/oracle9i/app
dev.920/a96620/xdb03usg.htm1656
http//www.acs.ilstu.edu/docs/oracle/server.101/b1
0759/functions204.htm