This discussion is archived
11 Replies Latest reply: May 31, 2013 7:29 AM by user4531996 RSS

Get info from an XML column in a tabular format

user4531996 Newbie
Currently Being Moderated
I got this sample working:

with sample_data
as (select xmltype ('
<gifts>
<giftinfo gifter="SANTA CLAUS"><Recipients><kid name="JOHN GOODBOY"/><kid name="CLARK GETCOAL"/></Recipients></giftinfo>
<giftinfo gifter="TOOTH FAIRY"><Recipients><kid name="PAUL TOOTHLESS"/></Recipients></giftinfo>
<giftinfo gifter="THREE WISE MEN"><Recipients><kid name="TONNY CAMEL"/><kid name="VICTOR HORSERIDER"/><kid name="CHARLES DELEPHANT"/></Recipients></giftinfo>
<giftinfo gifter="EASTER BUNNY"><Recipients><kid name="JIMMY EGGHEAD"/></Recipients></giftinfo>
</gifts>') xmlcol
from dual)
select extractvalue (value (x), '/giftinfo/@gifter') powerhouse,
extractvalue (value (x), '/giftinfo/Recipients/kid[2]/@name') kidname,
'Holiday' environment
from sample_data t,
table (xmlsequence (extract (t.xmlcol, '/gifts/giftinfo'))) x
where extractvalue (value (x), '/giftinfo/Recipients/kid[2]/@name')
is not null

I get these results:
POWERHOUSE     KIDNAME     ENVIRONMENT
SANTA CLAUS     CLARK GETCOAL     Holiday
THREE WISE MEN     VICTOR HORSERIDER     Holiday

Now, I need all kid's names at once, without the need to index them with [2], is there a way to do this?

Edited by: user4531996 on May 29, 2013 1:00 PM

I tried that before, I get this error message:
ORA-19025: EXTRACTVALUE returns value of only one node
  • 1. Re: Get info from an XML column in a tabular format
    991448 Journeyer
    Currently Being Moderated
    try this
    /giftinfo/Recipients//kid

    //     Selects nodes in the document from the current node that match the selection no matter where they are.
  • 2. Re: Get info from an XML column in a tabular format
    991448 Journeyer
    Currently Being Moderated
    try this
    /giftinfo/Recipients//kid@name

    //     Selects nodes in the document from the current node that match the selection no matter where they are.
  • 3. Re: Get info from an XML column in a tabular format
    user4531996 Newbie
    Currently Being Moderated
    I tried that before, I get this error message:
    ORA-19025: EXTRACTVALUE returns value of only one node
  • 4. Re: Get info from an XML column in a tabular format
    991448 Journeyer
    Currently Being Moderated
    /giftinfo/Recipients//@name

    http://www.w3schools.com/xpath/xpath_syntax.asp

    //@lang     Selects all attributes that are named lang
  • 5. Re: Get info from an XML column in a tabular format
    user4531996 Newbie
    Currently Being Moderated
    I get the same error. EXTRACTVALUE can only handle one occurrence at a time.
    I modified the query a little bit to use a different format of the extract function. I get a different result (columns kidnames), which contains the concatenation of all the required values.
    That is an interesting result by itself, but I need those values separated in different rows.

    with sample_data
    as (select xmltype (
    '
    <gifts>
    <giftinfo gifter="SANTA CLAUS"><Recipients><kid name="JOHN GOODBOY"/><kid name="CLARK GETCOAL"/></Recipients></giftinfo>
    <giftinfo gifter="TOOTH FAIRY"><Recipients><kid name="PAUL TOOTHLESS"/></Recipients></giftinfo>
    <giftinfo gifter="THREE WISE MEN"><Recipients><kid name="TONNY CAMEL"/><kid name="VICTOR HORSERIDER"/><kid name="CHARLES DELEPHANT"/></Recipients></giftinfo>
    <giftinfo gifter="EASTER BUNNY"><Recipients><kid name="JIMMY EGGHEAD"/></Recipients></giftinfo>
    </gifts>')
    xmlcol
    from dual)
    select extractvalue (value (x), '/giftinfo/@gifter') powerhouse,
    extractvalue (value (x), '/giftinfo/Recipients/kid[2]/@name') kidname,
    value (x).extract ('/giftinfo/Recipients//@name').getstringval ()
    kidnames,
    'Holiday' environment
    from sample_data t,
    table (xmlsequence (extract (t.xmlcol, '/gifts/giftinfo'))) x

    Results
    POWERHOUSE     KIDNAME               KIDNAMES                         ENVIRONMENT
    SANTA CLAUS     CLARK GETCOAL          JOHN GOODBOYCLARK GETCOAL               Holiday
    TOOTH FAIRY                    PAUL TOOTHLESS                         Holiday
    THREE WISE MEN     VICTOR HORSERIDER     TONNY CAMELVICTOR HORSERIDERCHARLES DELEPHANT     Holiday
    EASTER BUNNY                    JIMMY EGGHEAD                         Holiday
  • 6. Re: Get info from an XML column in a tabular format
    user4531996 Newbie
    Currently Being Moderated
    Easier to read, maybe:

    POWERHOUSE     |KIDNAME          |KIDNAMES                              |ENVIRONMENT
    SANTA CLAUS     | CLARK GETCOAL     | JOHN GOODBOYCLARK GETCOAL                    | Holiday
    TOOTH FAIRY     |                | PAUL TOOTHLESS                         | Holiday
    THREE WISE MEN     | VICTOR HORSERIDER     | TONNY CAMELVICTOR HORSERIDERCHARLES DELEPHANT     | Holiday
    EASTER BUNNY     |                | JIMMY EGGHEAD                         | Holiday
  • 7. Re: Get info from an XML column in a tabular format
    Jason_(A_Non) Expert
    Currently Being Moderated
    What is your version of Oracle as shown by
    select * from v$version;

    What do you want your output to look like based on this sample?

    Always a good reference to remember
    {message:id=9360002}

    I now picked up you mentioned separate rows, so assuming your version is >= 10.2, the answer will involve XMLTable (two of them actually). You could do it all in one XMLTable via XQuery if needed, but I will wait to see your answers to the two questions I asked.
  • 8. Re: Get info from an XML column in a tabular format
    user4531996 Newbie
    Currently Being Moderated
    Too focus on the issue, I forgot the obvious.

    The current version is Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

    Expected results would look like these:

    POWERHOUSE     |KIDNAME          |ENVIRONMENT
    EASTER BUNNY     | JIMMY EGGHEAD     | Holiday
    SANTA CLAUS     | CLARK GETCOAL     | Holiday
    SANTA CLAUS     | JOHN GOODBOY     | Holiday
    THREE WISE MEN     | CHARLES DELEPHANT     | Holiday
    THREE WISE MEN     | TONNY CAMEL     | Holiday
    THREE WISE MEN     | VICTOR HORSERIDER     | Holiday
    TOOTH FAIRY     | PAUL TOOTHLESS     | Holiday
  • 9. Re: Get info from an XML column in a tabular format
    Jason_(A_Non) Expert
    Currently Being Moderated
    Here's the two XMLTable version
    with sample_data
    as (select xmltype (
    '<gifts>
    <giftinfo gifter="SANTA CLAUS"><Recipients><kid name="JOHN GOODBOY"/><kid name="CLARK GETCOAL"/></Recipients></giftinfo>
    <giftinfo gifter="TOOTH FAIRY"><Recipients><kid name="PAUL TOOTHLESS"/></Recipients></giftinfo>
    <giftinfo gifter="THREE WISE MEN"><Recipients><kid name="TONNY CAMEL"/><kid name="VICTOR HORSERIDER"/><kid name="CHARLES DELEPHANT"/></Recipients></giftinfo>
    <giftinfo gifter="EASTER BUNNY"><Recipients><kid name="JIMMY EGGHEAD"/></Recipients></giftinfo>
    </gifts>')
    xmlcol
    from dual)
    select x1.powerhouse,
           x2.kidname,
           'Holiday' environment
    from sample_data t,
         XMLTable('/gifts/giftinfo'
                  PASSING t.xmlcol
                  COLUMNS
                  powerhouse   VARCHAR2(20) PATH '@gifter',
                  kidxml       XMLTYPE      PATH 'Recipients/kid') x1,
         XMLTable('/kid'
                  PASSING x1.kidxml
                  COLUMNS
                  kidname      VARCHAR2(20) PATH '@name') x2;
    which outputs
    POWERHOUSE           KIDNAME              ENVIRONMENT
    -------------------- -------------------- -----------
    SANTA CLAUS          JOHN GOODBOY         Holiday
    SANTA CLAUS          CLARK GETCOAL        Holiday
    TOOTH FAIRY          PAUL TOOTHLESS       Holiday
    THREE WISE MEN       TONNY CAMEL          Holiday
    THREE WISE MEN       VICTOR HORSERIDER    Holiday
    THREE WISE MEN       CHARLES DELEPHANT    Holiday
    EASTER BUNNY         JIMMY EGGHEAD        Holiday
    Note: You can always include x1.kidxml in the SELECT list if you want to see what that intermediate XMLType looks like.

    I'll include a single XQUERY/XMLTable version here in a bit.

    Edited by: A_Non on May 29, 2013 2:13 PM
    Forgot to mention the methods you are using, extract and extractValue are deprecated starting in 11.2.
  • 10. Re: Get info from an XML column in a tabular format
    Jason_(A_Non) Expert
    Currently Being Moderated
    And here's one XQuery/XMLTable version. I know other variations exist, given all that is possible with XQuery.
    select x1.powerhouse,
           x1.kidname,
           'Holiday' environment
    from sample_data t,
         XMLTable('for $i in /gifts/giftinfo
                     for $j in $i/Recipients/kid
                   return element r {
                     element powerhouse {data($i/@gifter)},
                     element kidname {data($j/@name)}
                   }'
                  PASSING t.xmlcol
                  COLUMNS
                  powerhouse   VARCHAR2(20) PATH 'powerhouse',
                  kidname      VARCHAR2(20) PATH 'kidname') x1;
  • 11. Re: Get info from an XML column in a tabular format
    user4531996 Newbie
    Currently Being Moderated
    I will give this last one a try, but the previous version worked like a charm.
    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points