Document Manipulation Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Document manipulation

Oracle SQL supplies functions to modify the document. Even if the functions are called INSERT or UPDATE or DELETE, they do not change the data in the table, but they modify the columns returned by the query.

UPDATEXML searches for an XPATH expression and updates it.

SELECT
   EXTRACT(
      UPDATEXML
      (
         OBJECT_VALUE,
         '//DETAILS[ENTRY="1291-01-01"]/ENTRY',
         XMLELEMENT(ENTRY, TO_DATE('01-08-1291','DD-MM-YYYY'))
      )
      , '//CANTON[NAME="Uri"]'
   ) URI
FROM
   WORLD;

URI
---------------------------------
<CANTON ID="UR">
   <NAME>Uri</NAME>
   <DETAILS>
      <ENTRY>1291-08-01</ENTRY>
      <LANGUAGE>German</LANGUAGE>
   </DETAILS>
</CANTON>

In the WORLD table, each entry of 1291-01-01 is updated by a new date. In the latest versions of Oracle, the XML dates are stored according to the XML convention SYYYY‑MM‑DD for dates and SYYYY‑MM‑DD't?HH24:MI:SSXFF for timestamps. In earlier releases, the date was stored according to the NLS settings.

UPDATEXML can change any element of the document.

SELECT
   UPDATEXML
   (
      XMLTYPE
      (
         CURSOR
         (
            SELECT
               *
            FROM
               DEPT
          )
      ),
      '//ROW[DEPTNO=10]/LOC',
      XMLTYPE('<CLOSED/>')
   ) DEPT
FROM
   DUAL; 

DEPT
-----------------------------------
<?xml version="1.0"?>
<ROWSET>
   <ROW>
      <DEPTNO>10</DEPTNO>
      <DNAME>ACCOUNTING</DNAME>
      <CLOSED/>
   </ROW>
   <ROW>
      <DEPTNO>20</DEPTNO>
      <DNAME>RESEARCH</DNAME>
      <LOC>DALLAS</LOC>
   </ROW>
   <ROW>
      <DEPTNO>30</DEPTNO>
      <DNAME>SALES</DNAME>
      <LOC>CHICAGO</LOC>
   </ROW>
   <ROW>
      <DEPTNO>40</DEPTNO>
      <DNAME>OPERATIONS</DNAME>
      <LOC>BOSTON</LOC>
   </ROW>
</ROWSET>

The location of department 10 is replaced by an empty tag. The underlying table DEPT is not changed, only the result set is modified.

INSERTXMLBEFORE inserts an XML element before the matched XPATH expression.

SELECT
   INSERTXMLBEFORE
   (
      XMLTYPE
      (
         CURSOR
         (
            SELECT
               ENAME,
               SAL
            FROM
               EMP
            WHERE
               DEPTNO=10
            ORDER BY
               SAL
          )
      ),
      '//ROW[SAL>=2000]/ENAME',
      XMLTYPE('<HIGH/>')
   ) DEPT10
FROM
   DUAL;

DEPT10
-------------------------------
<?xml version="1.0"?>
<ROWSET>
   <ROW>
      <ENAME>MILLER</ENAME>
      <SAL>1300</SAL>
   </ROW>
   <ROW>
      <HIGH/>
      <ENAME>CLARK</ENAME>
      <SAL>2450</SAL>
   </ROW>
   <ROW>
      <HIGH/>
      <ENAME>KING</ENAME>
      <SAL>5000</SAL>
   </ROW>
</ROWSET>

Each employee with a salary greater than or equal to 2000 gets a HIGH tag before his name.

INSERTCHILDXML(10gR2) appends a tag after the child elements of the XPATH expression.

SELECT
   INSERTCHILDXML
   (
      XMLTYPE
      (
         CURSOR
         (
            SELECT
               ENAME,
               SAL
            FROM
               EMP
            WHERE
               DEPTNO=10
            ORDER BY
               SAL
          )
      ),
      '//ROW[SAL>=2000]',
      'HIGH',
      XMLTYPE('<HIGH/>')
   ) INSERTCHILDXML
FROM
   DUAL;

INSERTCHILDXML
-------------------------------------------
<?xml version="1.0"?>
<ROWSET>
   <ROW>
      <ENAME>MILLER</ENAME>
      <SAL>1300</SAL>
   </ROW>
   <ROW>
      <ENAME>CLARK</ENAME>
      <SAL>2450</SAL>
      <HIGH/>
   </ROW>
   <ROW>
      <ENAME>KING</ENAME>
      <SAL>5000</SAL>
      <HIGH/>
   </ROW>
</ROWSET>  

INSERTCHILDXMLappends a HIGH tag after the child elements of ROW where the salary is greater than or equal to 2000.

When the element is prefixed with a @, it is treated as an attribute.

SELECT
   INSERTCHILDXML
   (
      XMLTYPE
      (
         CURSOR
         (
            SELECT
               DNAME
            FROM
               DEPT
            ORDER BY
               DEPTNO
         )
      ),
      '//ROW[4]',
      '@STATUS',
      'INACTIVE'
   ) DEPT
FROM
   DUAL;

DEPT
----------------------------------------
<?xml version="1.0"?>
<ROWSET>
   <ROW>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW>
      <DNAME>RESEARCH</DNAME>
   </ROW>
   <ROW>
      <DNAME>SALES</DNAME>
   </ROW>
   <ROW STATUS="INACTIVE">
      <DNAME>OPERATIONS</DNAME>
   </ROW>
</ROWSET>

A status is added to the 4^th row.

APPENDCHILDXMLappends XML content to a node. It works similar to INSERTCHILDXML; additionally, it can add different node types:

SELECT
   APPENDCHILDXML
   (
      OBJECT_VALUE,
      '/COUNTRY',
      XMLFOREST(7.5E6 AS POPULATION, '[+]' AS FLAG)
   ) APPENDCHILDXML
FROM
   WORLD;

APPENDCHILDXML
---------------------------------------------------
<COUNTRY ID="CH">
   <NAME>Switzerland</NAME>
   <CANTON_LIST>
      ...
   </CANTON_LIST>
   <POPULATION>7500000</POPULATION>
   <FLAG>[+]</FLAG>
</COUNTRY>

The content generated by the XMLFORESTis appended after the canton list where canton list is the last child of /COUNTRY.

DELETEXMLremoves elements from the document.

SELECT
   DELETEXML
   (
      DELETEXML
      (
         OBJECT_VALUE,
         '//CANTON[DETAILS//LANGUAGE!="French"]'
      ),
      '//DETAILS'
   )
FROM
   WORLD;

XMLSERIALIZE(DOCUMENTDELETEXML(D
--------------------------------
<COUNTRY ID="CH">
   <NAME>Switzerland</NAME>
   <CANTON_LIST>
      <CANTON ID="VD">
         <NAME>Vaud</NAME>
      </CANTON>
      <CANTON ID="NE">
         <NAME>Neuchatel</NAME>
      </CANTON>
      <CANTON ID="GE">
         <NAME>Geneva</NAME>
      </CANTON>
      <CANTON ID="JU">
         <NAME>Jura</NAME>
      </CANTON>
   </CANTON_LIST>
</COUNTRY>

The inner DELETEXML removes the cantons with a language different than French and the outer DELETEXML removes every DETAILS instance.

Pivot

The PIVOToperator in 11g aggregates multiple rows as columns. The PIVOT XML aggregates the rows as elements. In addition to the regular PIVOT syntax, PIVOT XML does not require explicit columns listing by allowing the ANY keyword.

SELECT
   *
FROM
(
   SELECT
      JOB,
      SAL
   FROM
      EMP
)
PIVOT XML
(
   MIN(SAL) AS "MIN SAL",
   MAX(SAL) AS "MAX SAL",
   COUNT(*) AS "COUNT"
FOR
   (JOB)
IN
   (ANY)
); 

JOB_XML
----------------------------------------------- 

<PivotSet>
   <item>
      <column name="JOB">ANALYST</column>
      <column name="MIN SAL">3000</column>
      <column name="MAX SAL">3000</column>
      <column name="COUNT">2</column>
   </item>
   <item>
      <column name="JOB">CLERK</column>
      <column name="MIN SAL">800</column>
      <column name="MAX SAL">1300</column>
      <column name="COUNT">4</column>
   </item>
   <item>
      <column name="JOB">MANAGER</column>
      <column name="MIN SAL">2450</column>
      <column name="MAX SAL">2975</column>
      <column name="COUNT">3</column>
   </item>
   <item>
      <column name="JOB">PRESIDENT</column>
      <column name="MIN SAL">5000</column>
      <column name="MAX SAL">5000</column>
      <column name="COUNT">1</column>
   </item>
   <item>
      <column name="JOB">SALESMAN</column>
      <column name="MIN SAL">1250</column>
      <column name="MAX SAL">1600</column>
      <column name="COUNT">4</column>
   </item>
</PivotSet>

For each job, the minimum and maximum salary as well as the count of employees is returned. 

Conclusion

This chapter is a detailed overview of XML and its various components, including new additions in 11g.  Definitions and examples are given for XML Instance, XMLTYPE, XMLELEMENT, XPATH expressions, XQuery and other elements.  Examples are also given for well-formed content and documents as well as ones that are not well-formed.