XML Exercise and Solution 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.

Exercises

1.      XMLTYPE

What are the different arguments accepted by the XMLTYPE constructor?

2.      XMLELEMENT

What is the difference between the two following expressions?

SELECT
   XMLELEMENT(DUMMY, DUMMY) EXPR1,
   XMLELEMENT(EVALNAME DUMMY, DUMMY) EXPR2
FROM
   DUAL;

3.      Concatenation

What is the difference between the two following queries?

SELECT
   XMLFOREST(ENAME, EMPNO) EXPR1
FROM
   EMP;

SELECT
   XMLCONCAT(ENAME, EMPNO) EXPR2
FROM
   EMP;

4.      XPATH

Explain the difference between / and //.

SELECT
   EXTRACT(OBJECT_VALUE,'/COUNTRY/NAME') NAME,
   EXTRACT(OBJECT_VALUE,'//CANTON//ENTRY') ENTRY
FROM
   WORLD;

5.      XMLSEQUENCE

What is the result of the following query?

SELECT
   COUNT(*)
FROM
   WORLD,
   TABLE(XMLSEQUENCE(EXTRACT(OBJECT_VALUE,'/COUNTRY/CANTON_LIST/CANTON')));

6.      XQUERY

What is the result of the following query?

SELECT
   *
FROM
   XMLTABLE
   (
      '
      for $I in 1 to 10
      let $I := $J*$I
      return <I>{$I}</I>
      '
      PASSING 2 AS J
      COLUMNS I NUMBER PATH '/I'
   );

7.      Aggregation

Compare the following queries:

SELECT
   XMLAGG(XMLELEMENT(EVALNAME 'DEPT_'||DEPTNO,SUM(SAL)))
FROM
   EMP
GROUP BY
   DEPTNO; 

SELECT
   DEPTNO_XML
FROM
(
   SELECT
      DEPTNO,
      SAL
   FROM
      EMP
)
PIVOT XML
(
   SUM(SAL)
   FOR
      (DEPTNO)
   IN
      (ANY)
);

8.      Cast

Compare the two following queries. What are the expected formats for ENTRY?

SELECT
   XMLCAST(EXTRACT(OBJECT_VALUE,'//CANTON[@ID="GE"]//ENTRY') AS DATE)
FROM
   WORLD;

SELECT
   CAST(EXTRACTVALUE(OBJECT_VALUE,'//CANTON[@ID="GE"]//ENTRY') AS DATE)
FROM
   WORLD;

9.      Document manipulation

In the WORLD table, delete all cantons except Geneva (GE) and delete all details.

Solutions

1.      XMLTYPE

What are the different arguments accepted by the XMLTYPE constructor?

The XMLTYPE constructor accepts CLOB, BLOB, BFILE, VARCHAR2 and REF Cursor.

2.      XMLELEMENT

What is the difference between the two following expressions?

SELECT
   XMLELEMENT(DUMMY, DUMMY) EXPR1,
   XMLELEMENT(EVALNAME DUMMY, DUMMY) EXPR2
FROM
   DUAL;

The first argument is the name of the tag. When prefixed by EVALNAME, the tag name is evaluated dynamically.

EXPR1                EXPR2
-------------------- --------------------
<DUMMY>X</DUMMY>     <X>X</X>

3.      Concatenation

What is the difference between the two following queries?

SELECT
   XMLFOREST(ENAME, EMPNO)
FROM
   EMP; 

XMLFOREST(ENAME,EMPNO)
----------------------------------------------------------------------
<ENAME>SMITH</ENAME><EMPNO>7369</EMPNO>
<ENAME>ALLEN</ENAME><EMPNO>7499</EMPNO>
<ENAME>WARD</ENAME><EMPNO>7521</EMPNO>
<ENAME>JONES</ENAME><EMPNO>7566</EMPNO>
<ENAME>MARTIN</ENAME><EMPNO>7654</EMPNO>
<ENAME>BLAKE</ENAME><EMPNO>7698</EMPNO>
<ENAME>CLARK</ENAME><EMPNO>7782</EMPNO>
<ENAME>SCOTT</ENAME><EMPNO>7788</EMPNO>
<ENAME>KING</ENAME><EMPNO>7839</EMPNO>
<ENAME>TURNER</ENAME><EMPNO>7844</EMPNO>
<ENAME>ADAMS</ENAME><EMPNO>7876</EMPNO>
<ENAME>JAMES</ENAME><EMPNO>7900</EMPNO>
<ENAME>FORD</ENAME><EMPNO>7902</EMPNO>
<ENAME>MILLER</ENAME><EMPNO>7934</EMPNO>

XMLFOREST accepts any datatypes as arguments.

SELECT
   XMLCONCAT(ENAME, EMPNO)
FROM
   EMP;
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected - got -

XMLCONCAT accepts only arguments of XMLTYPE. ENAME is a VARCHAR2 column and EMPNO a number.

4.      XPATH

Explain the difference between / and //.

SELECT
   EXTRACT(OBJECT_VALUE,'/COUNTRY/NAME') NAME,
   EXTRACT(OBJECT_VALUE,'//CANTON//ENTRY') ENTRY
FROM
   WORLD; 

NAME                      ENTRY
------------------------- --------------------------------------------------
<NAME>Switzerland</NAME>  <ENTRY>1351-01-01</ENTRY><ENTRY>1353-01-01</ENTRY>
                          <ENTRY>1332-01-01</ENTRY><ENTRY>1291-01-01</ENTRY>
                          <ENTRY>1291-01-01</ENTRY><ENTRY>1291-01-01</ENTRY>
                          <ENTRY>1291-01-01</ENTRY><ENTRY>1352-01-01</ENTRY>
                          <ENTRY>1352-01-01</ENTRY><ENTRY>1481-01-01</ENTRY>
                          <ENTRY>1481-01-01</ENTRY><ENTRY>1501-01-01</ENTRY>
                          <ENTRY>1501-01-01</ENTRY><ENTRY>1501-01-01</ENTRY>
                          <ENTRY>1513-01-01</ENTRY><ENTRY>1513-01-01</ENTRY>
                          <ENTRY>1803-01-01</ENTRY><ENTRY>1803-01-01</ENTRY>
                          <ENTRY>1803-01-01</ENTRY><ENTRY>1803-01-01</ENTRY>
                          <ENTRY>1803-01-01</ENTRY><ENTRY>1803-01-01</ENTRY>
                          <ENTRY>1815-01-01</ENTRY><ENTRY>1815-01-01</ENTRY>
                          <ENTRY>1815-01-01</ENTRY><ENTRY>1979-01-01</ENTRY>

/ denotes an absolute path where country is the root element and name is a top element directly below country. // is searched in the path, canton is searched in the whole document and entry is searched below the canton in elements and sub-elements.

It is recommended to use / whenever possible because // could imply a higher memory, CPU consumption and response time.

5.      XMLSEQUENCE

What is the result of the following query?

SELECT
   COUNT(*)
FROM
   WORLD,
   TABLE(XMLSEQUENCE(EXTRACT(OBJECT_VALUE,'/COUNTRY/CANTON_LIST/CANTON'))); 

  COUNT(*)
----------
        26

The XMLTYPE collection of cantons is unnested and the number of cantons is returned.

6.      XQUERY

What is the result of the following query?

SELECT
   *
FROM
   XMLTABLE
   (
      '
      for $I in 1 to 10
      let $I := $J*$I
      return <I>{$I}</I>
      '
      PASSING 2 AS J
      COLUMNS I NUMBER PATH '/I'
   );

         I
----------
         2
         4
         6
         8
        10
        12
        14
        16
        18
        20

The for loop generates 10 rows with values of $I from 1 to 10. The value 2 is passed to the variable $J and each value of $I is multiplied by $J, so a list of even numbers is returned.

7.      Aggregation

Compare the following queries:

SELECT
   XMLAGG(XMLELEMENT(EVALNAME 'DEPT_'||DEPTNO,SUM(SAL)))
FROM
   EMP
GROUP BY
   DEPTNO;

XMLAGG(XMLELEMENT(EVALNAME
--------------------------
<DEPT_10>8750</DEPT_10>
<DEPT_20>10875</DEPT_20>
<DEPT_30>9400</DEPT_30>

SELECT
   DEPTNO_XML
FROM
(
   SELECT
      DEPTNO,
      SAL
   FROM
      EMP
)
PIVOT XML
(
   SUM(SAL)
   FOR
      (DEPTNO)
   IN
      (ANY)
);

DEPTNO_XML
---------------------------------------------
<PivotSet>
   <item>
      <column name="DEPTNO">10</column>
      <column name="SUM(SAL)">8750</column>
   </item>
   <item>
      <column name="DEPTNO">20</column>
      <column name="SUM(SAL)">10875</column>
   </item>
   <item>
      <column name="DEPTNO">30</column>
      <column name="SUM(SAL)">9400</column>
   </item>
</PivotSet>

The queries are very similar; for each department, the salary is summed and an XML type is returned. Note the first query aggregates the XML elements in an XML content with no root element, whereas PivotSet is a well-formed document.

8.      Cast

Compare the two following queries. What are the expected formats for ENTRY?

SELECT
   XMLCAST(EXTRACT(OBJECT_VALUE,'//CANTON[@ID="GE"]//ENTRY') AS DATE)
FROM
   WORLD;

XMLCAST(EXTRACT(OBJECT_VALUE,
-----------------------------
Sunday, January 01, 1815

The ENTRY date is in the XML format SYYYY-MM-DD.

SELECT
   CAST(EXTRACTVALUE(OBJECT_VALUE,'//CANTON[@ID="GE"]//ENTRY') AS DATE)
FROM
   WORLD;

ERROR at line 2:
ORA-01846: not a valid day of the week

The ENTRY is not compatible with the session setting for NLS_DATE_FORMAT. To cast the date with a non-XML function, it is possible to use TO_DATE.

SELECT
   TO_DATE
   (
      EXTRACTVALUE(OBJECT_VALUE,'//CANTON[@ID="GE"]//ENTRY'),
      'SYYYY-MM-DD'
   )
FROM
   WORLD;

9.      Document manipulation

In the WORLD table, delete all cantons except Geneva (GE) and delete all details.

SELECT
   DELETEXML
   (
      DELETEXML
      (
         OBJECT_VALUE,'//CANTON[@ID!="GE"]'
      ), '//DETAILS'
   )
FROM
   WORLD;

DELETEXML(DELETEXML(OBJECT_VALUE
--------------------------------
<COUNTRY ID="CH">
   <NAME>Switzerland</NAME>
   <CANTON_LIST>
      <CANTON ID="GE">
         <NAME>Geneva</NAME>
      </CANTON>
   </CANTON_LIST>
</COUNTRY>

The inner DELETEXML deletes all cantons where the attribute ID is not equal to GE. The outer DELETEXML deletes the details. This, however, did not delete anything in the table. To update the node, use UPDATE.

UPDATE
   WORLD
SET
   OBJECT_VALUE
   =
   DELETEXML
   (
      DELETEXML
      (
         OBJECT_VALUE,'//CANTON[@ID!="GE"]'
      ), '//DETAILS'
   );