1 / 25

XML and ORACLE

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

brendy
Download Presentation

XML and ORACLE

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. 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('<Warehouse whNo="100"><Building>Owned</Building></Warehouse>')); select * from ejemplo1;

  5. Inserting Values on Ejemplo1 INSERT INTO ejemplo1 VALUES(101, XMLType('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd"> <Reference>ADAMS-20011127121040988PST</Reference> <Actions> <Action> <User>SCOTT</User> <Date>2002-03-31</Date> </Action> </Actions> <Reject/> <Requestor>Julie P. Adams</Requestor> <User>ADAMS</User> <CostCenter>R20</CostCenter> <ShippingInstructions> <name>Julie P. Adams</name> <address>Redwood Shores, CA 94065</address> <telephone>650 506 7300</telephone> </ShippingInstructions> <SpecialInstructions>Ground</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>The Ruling Class</Description> <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>Diabolique</Description> <Part Id="037429135020" UnitPrice="29.95" Quantity="3"/> </LineItem> <LineItem ItemNumber="3"> <Description>8 1/2</Description> <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder> ')); 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,'/PurchaseOrder[Reference="ADAMS-20011127121040988PST"]')‏ FROM ejemplo1; SELECT existsNode(XMLCOLUMN, '/PurchaseOrder/LineItems/LineItem[2]/Part[@Id="037429135020"]')‏ FROM ejemplo1; SELECT existsNode(XMLCOLUMN, '/PurchaseOrder/LineItems/LineItem[Description="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 > 100; XMLElement(): Generating Nested XML SELECT XMLELEMENT("Emp", XMLELEMENT("name", XMLCOLUMN), XMLELEMENT ( "emp", KEYVALUE)) AS "result" FROM ejemplo1 WHERE KEYVALUE > 100 ; XMLElement(): Generating an Element for Each Employee with ID Attribute SELECT XMLELEMENT("Emp", XMLATTRIBUTES(KEYVALUE AS "ID") ) AS "result" FROM ejemplo1 WHERE KEYVALUE > 100;

  9. XML EXTRACT SELECT extract(XMLCOLUMN,'/emp//enumber') FROM ejemplo1; Using existsNode() in the WHERE Clause SELECT count(*) FROM ejemplo1 WHERE existsNode(XMLCOLUMN,'/PurchaseOrder[User="ADAMS"]') = 1; Using delete in the WHERE Clause DELETE FROM ejemplo1 WHERE existsNode(XMLCOLUMN,'/PurchaseOrder[User="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 > 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>

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

  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 > 200 ; RESULT ----------------------------------------------------------------------- <Emp><name>Michael Hartstein</name><hiredate>1996-02-17</hiredate></Emp> <Emp><name>Pat Fay</name><hiredate>1997-08-17</hiredate></Emp> <Emp><name>Susan Mavris</name><hiredate>1994-06-07</hiredate></Emp> <Emp><name>Hermann Baer</name><hiredate>1994-06-07</hiredate></Emp>

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

  14. XMLELEMENT: Generating an Element from a User-Defined Datatype Instance CREATE OR REPLACE TYPE emp_t AS OBJECT ("@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 ("@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 ------------- <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>

  16. Accessing a Text Node Value Matching an XPath Expression Using extractValue()‏ SELECT extractValue(XMLCOLUMN,'/PurchaseOrder/Reference') FROM ejemplo1; SELECT extractValue(XMLCOLUMN,'/Description')‏ FROM ejemplo1, TABLE ( xmlsequence (extract(XMLCOLUMN,'/PurchaseOrder/LineItems/LineItem/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/LineItem[2]'. UPDATE ejemplo1 SET xmlcolumn = updateXML(xmlcolumn, '/PurchaseOrder/LineItems/LineItem[2]', xmltype('<LineItem ItemNumber="4"> <Description>Andrei Rublev</Description> <Part Id="715515009928" UnitPrice="39.95" Quantity="2"/> </LineItem>' )‏ ) WHERE existsNode(XMLCOLUMN, '/PurchaseOrder[Reference="MILLER-200203311200000000PST"]' ) = 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,'/PurchaseOrder/Reference/text()', 'MILLER-200203311200000000PST')‏ WHERE existsNode(XMLCOLUMN,'/PurchaseOrder[Reference="ADAMS-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 ------------------------------------- <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>

  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_id=10; DEPTXML --------------------------------- <Department DEPTNO="10"> <DNAME>Administration</DNAME> <EMP_LIST> <EMP_T EMPNO="200"> <ENAME>Whalen</ENAME> </EMP_T> </EMP_LIST> </Department>

  21. XMLFOREST SELECT XMLELEMENT("Emp", XMLFOREST(KEYVALUE, XMLCOLUMN))‏ "Emp Element" FROM ejemplo1 WHERE KEYVALUE > 99;

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

  23. XMLTRANSFORM Inserting StyleSheet: INSERT INTO xsl_tab VALUES (XMLTYPE.createxml('<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.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>'));

  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/appdev.920/a96620/xdb03usg.htm#1656 http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/functions204.htm

More Related