Oracle XML Operations XMLType, UPDATEXML, EXISTSNODE, XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLAGG, XMLROOT and Basic XML Parsing via PL/SQL

XMLType

CREATE TABLE myTable(

    id  NUMBER PRIMARY KEY,

    emps XMLType NOT NULL

);

INSERT INTO myTable VALUES

(1, xmltype('<?xml version="1.0" standalone="no" ?>

<emps>

    <emp>

        <home_address>address</home_address>

    </emp>

</emps>')

);

UPDATEXML

update myTable

set emps = updatexml(emps, '/emps/emp/home_address/text()','New York')

where id = 1;

CREATE TABLE myTable

  (myID            NUMBER PRIMARY KEY,

   myValue     XMLTYPE )

   XMLTYPE myValue STORE AS CLOB

/

EXISTSNODE

select COUNT(*)

from myTable d

where existsnode(myValue, '/ROWSET') = 1

/

XMLELEMENT

SELECT XMLELEMENT("employee",

         XMLATTRIBUTES(e.empno AS "works_number"),

         XMLELEMENT("name",e.ename),

         XMLELEMENT("job",e.job)

       ) AS employee

FROM   emp e

WHERE  e.empno = 7782;

EMPLOYEE

-----------------------------------------------------------------------------

<employee works_number="7782"><name>CLARK</name><job>MANAGER</job></employee>

XMLATTRIBUTES

The XMLATRIBUTES function converts column data into attributes of the parent element. The function call should contain one or more columns in a comma separated list. The attribute names will match the column names using the default uppercase unless an alias is used.

SELECT XMLELEMENT("employee",

         XMLATTRIBUTES(

           e.empno AS "works_number",

           e.ename AS "name")

       ) AS employee

FROM   emp e

WHERE  e.empno = 7782;

EMPLOYEE

-----------------------------------------------------------------------------

<employee works_number="7782" name="CLARK"></employee>

XMLFOREST

Using XMLELEMENT to deal with lots of columns is rather clumsy. Like XMLATTRIBUTES, the XMLFOREST function allows you to process multiple columns at once.

SELECT XMLELEMENT("employee",

         XMLFOREST(

           e.empno AS "works_number",

           e.ename AS "name",

           e.job AS "job")

       ) AS employee

FROM   emp e

WHERE  e.empno = 7782;

EMPLOYEE

-----------------------------------------------------------------------------

<employee><works_number>7782</works_number><name>CLARK</name><job>MANAGER</job></employee>

1 row selected.

XMLAGG

So far we have just looked at creating individual XML fragments. What happens if we start dealing with multiple rows of data?

SELECT XMLELEMENT("employee",

         XMLFOREST(

           e.empno AS "works_number",

           e.ename AS "name")

       ) AS employees

FROM   emp e

WHERE  e.deptno = 10;

EMPLOYEES

-----------------------------------------------------------------------------

<employee><works_number>7782</works_number><name>CLARK</name></employee>

<employee><works_number>7839</works_number><name>KING</name></employee>

<employee><works_number>7934</works_number><name>MILLER</name></employee>

3 rows selected.

We got the XML we wanted, but it is returned as three fragments in three separate rows. The XMLAGG function allows is to aggregate these separate fragments into a single fragment. In the following example we can see the three fragments are now presented in a single row.

SELECT XMLAGG(

         XMLELEMENT("employee",

           XMLFOREST(

             e.empno AS "works_number",

             e.ename AS "name")

         )

       ) AS employees

FROM   emp e

WHERE  e.deptno = 10;

EMPLOYEE

-----------------------------------------------------------------------------

<employee><works_number>7782</works_number><name>CLARK</name></employee><employee><works_number>7839

</works_number><name>KING</name></employee><employee><works_number>7934</works_number><name>MILLER</

name></employee>

1 row selected.

XMLROOT

The XMLROOT function allows us to place an XML tag at the start of our XML document.

SELECT XMLROOT(

         XMLELEMENT("employees",

           XMLAGG(

             XMLELEMENT("employee",

               XMLFOREST(

                 e.empno AS "works_number",

                 e.ename AS "name")

             )

           )

         )

       ) AS employees

FROM   emp e

WHERE  e.deptno = 10;

EMPLOYEE

-----------------------------------------------------------------------------

<?xml version="1.0" encoding="US-ASCII"?>

<employees>

  <employee>

    <works_number>7782</works_number>

    <name>CLARK</name>

  </employee>

  <employee>

    <works_number>7839</works_number>

    <name>KING</name>

  </employee>

  <employee>

    <works_number>7934</works_number>

    <name>MILLER</name>

  </employee>

</employees>

1 row selected.

Basic XML Parsing via PL/SQL

DECLARE

l_doc       VARCHAR2(2000);

l_domdoc    dbms_xmldom.DOMDocument;

l_nodelist  dbms_xmldom.DOMNodeList;

l_node      dbms_xmldom.DOMNode;

l_value     VARCHAR2(30);

l_xmltype   XMLTYPE;

l_empx      XMLTYPE;

l_index     PLS_INTEGER;

l_col_ind   PLS_INTEGER;

BEGIN

l_doc := '<employees>

 <emp>

  <name>Scott</name>

  <favorites>

   <color>red</color>

   <color>orange</color>

  </favorites>

 </emp>

 <emp>

  <name>John</name>

  <favorites>

   <color>blue</color>

   <color>green</color>

  </favorites>

 </emp>

</employees>';

l_domdoc   := dbms_xmldom.newDomDocument(l_doc);

-- Method 1

dbms_output.put_line('Method 1');

l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/employees/emp/name');

FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP

 l_node := dbms_xmldom.item(l_nodelist, cur_emp);

 l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));

 dbms_output.put_line('Emp Name: '||l_value);

END LOOP;

-- Method 2

dbms_output.new_line;

dbms_output.put_line('Method 2');

l_nodelist  := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');

-- get first item from list, could loop as shown above

l_node      := dbms_xmldom.item(l_nodelist, 0);

l_value    := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));

dbms_output.put_line('Emp Name: '||l_value);

-- Done with DOMDocument examples, setup for XMLType based examples

dbms_xmldom.freeDocument(l_domdoc);

l_xmltype := XMLTYPE(l_doc);

-- Method 3

dbms_output.new_line;

dbms_output.put_line('Method 3');

l_index := 1;

WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0

LOOP

 l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();

 dbms_output.put_line('Emp Name: '||l_value);

 l_index := l_index + 1;

END LOOP;

-- Method 4

dbms_output.new_line;

dbms_output.put_line('Method 4');

l_value := l_xmltype.extract('/employees/emp[2]/name/text()').getStringVal();

dbms_output.put_line('Emp Name: '||l_value);

-- Method 5

dbms_output.new_line;

dbms_output.put_line('Method 5');

l_index := 1;

WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0

LOOP

 l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');

 l_col_ind := 1;

 WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0

 LOOP

    l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();

    dbms_output.put_line('Color: '||l_value);

    l_col_ind := l_col_ind + 1;

 END LOOP;

 l_index := l_index + 1;

END LOOP;

END;