1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle XML datatype

Discussion in 'General' started by tripower, Aug 25, 2010.

  1. tripower

    tripower Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Is the Oracle XML datatype available in 10g and if so what is it's advantages over using a CLOB datatype?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, it is. Advantages? The XML type can be accessed by built-in functions in the 10g kernel, rather than using external packages such as DBMS_LOB for CLOB manipulation. Examples are posted below:

    Code (SQL):

    SQL> --
    SQL> -- APPENDCHILDXML demo
    SQL> --
    SQL> -- Appends a user-supplied value onto the target XML as the child of the node indicated by an XPath expression
    SQL> --
    SQL> col "Prop.Owner" format a30
    SQL>
    SQL> SELECT warehouse_id, warehouse_name,
    2 EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') "Prop.Owner"
    3 FROM warehouses
    4 WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1;
    no ROWS selected
    SQL>
    SQL> UPDATE warehouses
    2 SET warehouse_spec = APPENDCHILDXML(warehouse_spec,
    3 'Warehouse/Building', XMLType('<Owner>Grandco</Owner>'))
    4 WHERE EXTRACTVALUE(warehouse_spec, '/Warehouse/Building') = 'Rented';
    2 ROWS updated.
    SQL>
    SQL> SELECT warehouse_id, warehouse_name,
    2 EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') "Prop.Owner"
    3 FROM warehouses
    4 WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1;
    WAREHOUSE_ID WAREHOUSE_NAME Prop.Owner
    ------------ ----------------------------------- ------------------------------
    2 San Francisco Grandco
    3 NEW Jersey Grandco
    2 ROWS selected.
    SQL>
    SQL> --
    SQL> -- DELETECHILDXML demo
    SQL> --
    SQL> -- Deletes the node or nodes matched by the XPath expression in the target XML
    SQL> --
    SQL> SELECT warehouse_id, warehouse_spec
    2 FROM warehouses
    3 WHERE warehouse_id IN (2,3);
    WAREHOUSE_ID WAREHOUSE_SPEC
    ------------ ------------------------------------------------------------
    2 <?xml version="1.0"?><Warehouse><Building>Rented<Owner>Grand
    co</Owner></Building><Area>50000</Area><Docks>1</Docks><Dock
    Type>Side load</DockType><WaterAccess>Y</WaterAccess><RailAc
    cess>N</RailAccess><Parking>Lot</Parking><VClearance>12 ft</
    VClearance></Warehouse>
    3 <?xml version="1.0"?><Warehouse><Building>Rented<Owner>Grand
    co</Owner></Building><Area>85700</Area><DockType/><WaterAcce
    ss>N</WaterAccess><RailAccess>N</RailAccess><Parking>Street<
    /Parking><VClearance>11.5 ft</VClearance></Warehouse>
     
    2 ROWS selected.
    SQL>
    SQL> UPDATE warehouses
    2 SET warehouse_spec=DELETEXML(warehouse_spec,'/Warehouse/Building/Owner')
    3 WHERE warehouse_id = 2;
    1 ROW updated.
    SQL>
    SQL> col warehouse_spec format a60
    SQL>
    SQL> SELECT warehouse_id, warehouse_spec
    2 FROM warehouses
    3 WHERE warehouse_id IN (2,3);
    WAREHOUSE_ID WAREHOUSE_SPEC
    ------------ ------------------------------------------------------------
    2 <?xml version="1.0"?><Warehouse><Building>Rented</Building><
    Area>50000</Area><Docks>1</Docks><DockType>Side load</DockTy
    pe><WaterAccess>Y</WaterAccess><RailAccess>N</RailAccess><Pa
    rking>Lot</Parking><VClearance>12 ft</VClearance></Warehouse
    >
    3 <?xml version="1.0"?><Warehouse><Building>Rented<Owner>Grand
    co</Owner></Building><Area>85700</Area><DockType/><WaterAcce
    ss>N</WaterAccess><RailAccess>N</RailAccess><Parking>Street<
    /Parking><VClearance>11.5 ft</VClearance></Warehouse>
     
    2 ROWS selected.
    SQL>
    SQL> --
    SQL> -- DEPTH demo
    SQL> --
    SQL> -- Returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable
    SQL> --
    SQL> SELECT PATH(1), DEPTH(2)
    2 FROM RESOURCE_VIEW
    3 WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1
    4 AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1;
    PATH(1)
    --------------------------------------------------------------------------------
    DEPTH(2)
    ----------
    localhost:8080
    1
    localhost:8080/SOURCE
    2
    localhost:8080/SOURCE/schemas
    3
    localhost:8080/SOURCE/schemas/poSource
    4
    localhost:8080/SOURCE/schemas/poSource/xsd
    5
    localhost:8080/SOURCE/schemas/poSource/xsd/purchaseOrder.xsd
    6
     
    6 ROWS selected.
    SQL>
    SQL> --
    SQL> -- EXISTSNODE demo
    SQL> --
    SQL> -- Determines whether traversal of an XML document using a specified path results in any nodes
    SQL> --
    SQL> col "Prop.Owner" format a30
    SQL>
    SQL> SELECT warehouse_id, warehouse_name,
    2 EXTRACTVALUE(warehouse_spec, '/Warehouse/Building/Owner') "Prop.Owner"
    3 FROM warehouses
    4 WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Building/Owner') = 1;
    WAREHOUSE_ID WAREHOUSE_NAME Prop.Owner
    ------------ ----------------------------------- ------------------------------
    3 NEW Jersey Grandco
    1 ROW selected.
    SQL>
    SQL> SELECT PATH(1), DEPTH(2)
    2 FROM RESOURCE_VIEW
    3 WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1
    4 AND UNDER_PATH(res, '/sys/schemas/OE', 2)=1;
    PATH(1)
    --------------------------------------------------------------------------------
    DEPTH(2)
    ----------
    localhost:8080
    1
    localhost:8080/SOURCE
    2
    localhost:8080/SOURCE/schemas
    3
    localhost:8080/SOURCE/schemas/poSource
    4
    localhost:8080/SOURCE/schemas/poSource/xsd
    5
    localhost:8080/SOURCE/schemas/poSource/xsd/purchaseOrder.xsd
    6
     
    6 ROWS selected.
    SQL>
    SQL> SELECT warehouse_id, warehouse_name
    2 FROM warehouses
    3 WHERE EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1;
    WAREHOUSE_ID WAREHOUSE_NAME
    ------------ -----------------------------------
    1 Southlake, Texas
    2 San Francisco
    4 Seattle, Washington
    3 ROWS selected.
    SQL>
    SQL> --
    SQL> -- EXTRACT demo
    SQL> --
    SQL> -- Applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment
    SQL> --
    SQL> col "Number of Docks" format a30
    SQL>
    SQL> SELECT warehouse_name,
    2 EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
    3 FROM warehouses
    4 WHERE warehouse_spec IS NOT NULL;
    WAREHOUSE_NAME NUMBER OF Docks
    ----------------------------------- ------------------------------
    Southlake, Texas <Docks>2</Docks>
    San Francisco <Docks>1</Docks>
    NEW Jersey
    Seattle, Washington <Docks>3</Docks>
    4 ROWS selected.
    SQL>
    SQL> --
    SQL> -- EXTRACTVALUE demo
    SQL> --
    SQL> -- Takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node
    SQL> --
    SQL> col docks format a30
    SQL>
    SQL> SELECT warehouse_name,
    2 EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks"
    3 FROM warehouses e
    4 WHERE warehouse_spec IS NOT NULL;
    WAREHOUSE_NAME Docks
    ----------------------------------- ------------------------------
    Southlake, Texas 2
    San Francisco 1
    NEW Jersey
    Seattle, Washington 3
    4 ROWS selected.
    SQL>
    SQL> --
    SQL> -- INSERTCHILDXML demo
    SQL> --
    SQL> -- Inserts a user-supplied value into the target XML at the node indicated by the XPath expression
    SQL> --
    SQL> SET long 1000000
    SQL>
    SQL> SELECT warehouse_spec
    2 FROM warehouses
    3 WHERE warehouse_id = 3;
    WAREHOUSE_SPEC
    ------------------------------------------------------------
    <?xml version="1.0"?><Warehouse><Building>Rented<Owner>Grand
    co</Owner></Building><Area>85700</Area><DockType/><WaterAcce
    ss>N</WaterAccess><RailAccess>N</RailAccess><Parking>Street<
    /Parking><VClearance>11.5 ft</VClearance></Warehouse>
     
    1 ROW selected.
    SQL>
    SQL> UPDATE warehouses
    2 SET warehouse_spec = INSERTCHILDXML(warehouse_spec,
    3 '/Warehouse/Building', 'Owner', XMLType('<Owner>LesserCo</Owner>'))
    4 WHERE warehouse_id = 3;
    1 ROW updated.
    SQL>
    SQL> SELECT warehouse_spec
    2 FROM warehouses
    3 WHERE warehouse_id = 3;
    WAREHOUSE_SPEC
    ------------------------------------------------------------
    <?xml version="1.0"?><Warehouse><Building>Rented<Owner>Grand
    co</Owner><Owner>LesserCo</Owner></Building><Area>85700</Are
    a><DockType/><WaterAccess>N</WaterAccess><RailAccess>N</Rail
    Access><Parking>Street</Parking><VClearance>11.5 ft</VCleara
    nce></Warehouse>
     
    1 ROW selected.
    SQL>
    SQL> --
    SQL> -- INSERTXMLBEFORE demo
    SQL> --
    SQL> -- Inserts a user-supplied value into the target XML before the node indicated by the XPath expression
    SQL> --
    SQL> SELECT warehouse_name, EXTRACT(warehouse_spec,
    2 '/Warehouse/Building/Owner') "Owners"
    3 FROM warehouses
    4 WHERE warehouse_id = 3;
    WAREHOUSE_NAME
    -----------------------------------
    Owners
    --------------------------------------------------------------------------------
    NEW Jersey
    <Owner>Grandco</Owner><Owner>LesserCo</Owner>
     
    1 ROW selected.
    SQL>
    SQL> UPDATE warehouses
    2 SET warehouse_spec = INSERTXMLBEFORE(warehouse_spec,
    3 '/Warehouse/Building/Owner[2]',
    4 XMLType('<Owner>ThirdOwner</Owner>'))
    5 WHERE warehouse_id = 3;
    1 ROW updated.
    SQL>
    SQL> SELECT warehouse_name, EXTRACT(warehouse_spec,
    2 '/Warehouse/Building/Owner') "Owners"
    3 FROM warehouses
    4 WHERE warehouse_id = 3;
    WAREHOUSE_NAME
    -----------------------------------
    Owners
    --------------------------------------------------------------------------------
    NEW Jersey
    <Owner>Grandco</Owner><Owner>ThirdOwner</Owner><Owner>LesserCo</Owner>
     
    1 ROW selected.
    SQL>
    SQL> --
    SQL> -- UPDATEXML demo
    SQL> --
    SQL> -- Takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value
    SQL> --
    SQL> SELECT warehouse_name,
    2 EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
    3 FROM warehouses
    4 WHERE warehouse_name = 'San Francisco';
    WAREHOUSE_NAME NUMBER OF Docks
    ----------------------------------- ------------------------------
    San Francisco <Docks>1</Docks>
    1 ROW selected.
    SQL>
    SQL> UPDATE warehouses
    2 SET warehouse_spec = UPDATEXML(warehouse_spec,
    3 '/Warehouse/Docks/text()',4)
    4 WHERE warehouse_name = 'San Francisco';
    1 ROW updated.
    SQL>
    SQL> SELECT warehouse_name,
    2 EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
    3 FROM warehouses
    4 WHERE warehouse_name = 'San Francisco';
    WAREHOUSE_NAME NUMBER OF Docks
    ----------------------------------- ------------------------------
    San Francisco <Docks>4</Docks>
    1 ROW selected.
    SQL>
    SQL> --
    SQL> -- XMLAGG demo
    SQL> --
    SQL> -- Takes a collection of XML fragments and returns an aggregated XML document. Any arguments that return null are dropped from the result
    SQL> --
    SQL> SET long 100000
    SQL>
    SQL> SELECT XMLELEMENT("Department", XMLAGG(XMLELEMENT("Employee",
    2 e.job_id||' '||e.last_name) ORDER BY last_name)) AS "Dept_list"
    3 FROM employees e
    4 WHERE e.department_id = 30;
    Dept_list
    --------------------------------------------------------------------------------
    <Department><Employee>PU_CLERK Baida</Employee><Employee>PU_CLERK Colmenares</Em
    ployee><Employee>PU_CLERK Himuro</Employee><Employee>PU_CLERK Khoo</Employee><Em
    ployee>PU_MAN Raphaely</Employee><Employee>PU_CLERK Tobias</Employee></Departmen
    t>
     
    1 ROW selected.
    SQL>
    SQL>
    SQL> --
    SQL> -- XMLCDATA demo
    SQL> --
    SQL> -- Generates a CDATA section by evaluating value_expr
    SQL> --
    SQL> SELECT XMLELEMENT("PurchaseOrder",
    2 XMLAttributes(dummy AS "pono"),
    3 XMLCdata('<!DOCTYPE po_dom_group [
    4 <!ELEMENT po_dom_group(student_name)*>
    5 <!ELEMENT po_purch_name (#PCDATA)>
    6 <!ATTLIST po_name po_no ID #REQUIRED>
    7 <!ATTLIST po_name trust_1 IDREF #IMPLIED>
    8 <!ATTLIST po_name trust_2 IDREF #IMPLIED>
    9 ]>'
    )) "XMLCData"
    10 FROM dual;
    XMLCData
    --------------------------------------------------------------------------------
    <PurchaseOrder pono="X"><![CDATA[<!DOCTYPE po_dom_group [
    <!ELEMENT po_dom_group(student_name)*>
    <!ELEMENT po_purch_name (#PCDATA)>
    <!ATTLIST po_name po_no ID #REQUIRED>
    <!ATTLIST po_name trust_1 IDREF #IMPLIED>
    <!ATTLIST po_name trust_2 IDREF #IMPLIED>
    ]>]]></PurchaseOrder>
     
    1 ROW selected.
    SQL>
    SQL> --
    SQL> -- XMLCOLATTVAL demo
    SQL> --
    SQL> -- Creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name
    SQL> --
    SQL> SELECT XMLELEMENT("Emp",
    2 XMLCOLATTVAL(e.employee_id, e.last_name, e.salary)) "Emp Element"
    3 FROM employees e
    4 WHERE employee_id = 204;
    Emp Element
    --------------------------------------------------------------------------------
    <Emp><COLUMN name = "EMPLOYEE_ID">204</column><COLUMN name = "LAST_NAME">Baer</c
    olumn><COLUMN name = "SALARY">10000</column></Emp>
     
    1 ROW selected.
    SQL>