11 Replies Latest reply: May 31, 2013 9:29 AM by user4531996 RSS

    Get info from an XML column in a tabular format

    user4531996
      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
          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
            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
              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
                /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
                  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
                    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)
                      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
                        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)
                          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)
                            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
                              I will give this last one a try, but the previous version worked like a charm.
                              Thanks!