Tuesday, July 20, 2010

Basic XML Parsing via XMLTable

In Basic XML Parsing via PL/SQL, I looked at "How do I parse this XML?" when the XML resides in PL/SQL. Oftentimes, the XML resides in the database and so Oracle provides another option for parsing the XML, the XMLTable command. This command became available with the release of 10.2. On versions <= 10.1, you have to use TABLE(xmlsequence(extract(...))). I might cover that at some point in the future but will see. While reading that documentation may make XMLTable seem complex, which it can be, it is very easy to use. Let us start with putting our XML into the DB via:
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE your_table
2 (xml_col XMLTYPE);

Table created

SQL>
SQL> INSERT INTO your_table
2 VALUES
3 ('<employees>
4 <emp>
5 <name>Scott</name>
6 <favorites>
7 <color>red</color>
8 <color>orange</color>
9 </favorites>
10 </emp>
11 <emp>
12 <name>John</name>
13 <favorites>
14 <color>blue</color>
15 <color>green</color>
16 </favorites>
17 </emp>
18 </employees>');

1 row inserted

SQL> commit;

Commit complete

Now let's get out the same data we did in the original PL/SQL example. For this we will only be doing Methods 3 - 5 since they cover Methods 1 and 2 as well.
SQL> -- Method 3
SQL> SELECT xt.nam
2 FROM your_table yt,
3 XMLTable('employees/emp'
4 PASSING yt.xml_col -- defines source of XMLType, can define a join
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
Scott
John

SQL> --
SQL> -- Method 4
SQL> SELECT xt.nam
2 FROM your_table yt,
3 XMLTable('employees/emp[2]'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name') xt;

NAM
--------------------
John

SQL> --
SQL> -- Method 5.a
SQL> SELECT xt.color
2 FROM your_table yt,
3 XMLTable('employees/emp/favorites/color'
4 PASSING yt.xml_col
5 COLUMNS
6 color VARCHAR2(10) PATH '.') xt;

COLOR
----------
red
orange
blue
green

SQL> --
SQL> -- Method 5.b
SQL> SELECT xt.nam, xt2.color
2 FROM your_table yt,
3 XMLTable('employees/emp'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name',
7 color_t XMLTYPE PATH 'favorites') xt, -- path to the node that repeats
8 XMLTable('favorites/color'
9 PASSING xt.color_t -- define input XMLType as output of above, aka a join
10 COLUMNS
11 color VARCHAR2(10) PATH '.') xt2;

NAM COLOR
-------------------- ----------
Scott red
Scott orange
John blue
John green

SQL> --
SQL> -- Method 5.c
SQL> SELECT xt.nam, xt2.color
2 FROM your_table yt,
3 XMLTable('employees/emp'
4 PASSING yt.xml_col
5 COLUMNS
6 nam VARCHAR2(20) PATH 'name',
7 color_t XMLTYPE PATH 'favorites/color') xt, -- path to the node that repeats
8 XMLTable('/color'
9 PASSING xt.color_t -- define input XMLType as output of above, aka a join
10 COLUMNS
11 color VARCHAR2(10) PATH '.') xt2;

NAM COLOR
-------------------- ----------
Scott red
Scott orange
John blue
John green

Methods 3 - 5.a here extract the same information as we did via PL/SQL.

Breaking down the Oracle documentation syntax, I will cover XMLnamespaces_clause in another post. The XQuery_string can be a simple XPath as shown above or more complex XQuery statements to be demonstrated later. The XML_passing_clause is just the PASSING line where the input XML is defined. This can join the XMLTable to another table/XMLTable, a variable, or be XML generated right there. The XML_table_column section following COLUMNS simply lists how to find the information referenced by the XQuery_string. The information is relative to the results of the XQuery_string.

Trouble shooting tips:
  • If you are unsure of what the XMLType looks like at some point within an XMLTable, simply create a column of XMLType and include it in the SELECT list. In example 5.c, adding xt.color_t to the select list allows us to see what Oracle was passing into the second XMLTable.

4 comments:

  1. It's awesome.. It works fine thanks

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Aim to extract the BRAND and its COST and it is getting extracted without any problem using

    CREATE TABLE xml_tab (seqno number default 1, xml_data XMLType);

    XMLTable.
    INSERT INTO xml_tab
    VALUES
    (1, XMLType('

    1000
    2000

    '));

    SELECT t.seqno, tx.* FROM xml_tab t,
    XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK' PASSING t.xml_data
    COLUMNS brandname CHAR(20) PATH '@BRAND',
    cost CHAR(20) PATH '.') tx
    WHERE brandname in ('HP', 'LENOVA') AND seqno = 1;

    Outpupt
    SEQNO BRANDNAME COST
    ---------- -------------------- --------------------
    1 HP 1000
    1 LENOVA 2000


    Below query will demonstrate the problem I am facing and need your kind help to resovle it.

    INSERT INTO xml_tab
    VALUES
    (2, XMLType('

    3000
    1400
    5000

    '));

    (Below query is not working)
    SELECT t.seqno, tx.* FROM xml_tab t,
    XMLTABLE('/PRODUCTS/LAPTOPS/NOTEBOOK[1]' PASSING t.xml_data
    COLUMNS brandname CHAR(20) PATH '@BRAND',
    cost CHAR(20) PATH '.') tx
    WHERE brandname in ('HP', 'LENOVA') AND seqno = 2;

    My requirement is to display only single row of HP and LENOVA brand even if there are multiple XML tags of one brand. Therefore, desired output is
    SEQNO BRANDNAME COST
    ---------- -------------------- --------------------
    2 HP 3000
    2 LENOVA 5000

    Kindly suggest.

    Thanks in advance
    Sandeep

    ReplyDelete