Oracle, XMLTABLE - extract data with list of values

Question

i have sample XML.

<a>
   <b>
      <id>1</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
  </b>
  <b>
  <id>2</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
</b>
    </a>

my desired output is

id  comment
1   asd
1   23
1   5436
1   123g
2   asd
2   23
2   5436
2   123g

i tried to achieve with xmltable:

SELECT  *
 
FROM    (

          SELECT  t1.* 
          FROM    XMLTABLE
                  (
                    '//a/b'
                    PASSING xmltype('       <a>
       <b>
          <id>1</id>
          <comment-list>
            <comment>
         <value>asd</value>
            </comment>
            <comment>
         <value>23</value>
            </comment>
            <comment>
         <value>5436</value>
            </comment>
            <comment>
         <value>123g</value>
            </comment>
          </comment-list>
      </b>
      <b>
      <id>2</id>
          <comment-list>
            <comment>
         <value>asd</value>
            </comment>
            <comment>
         <value>23</value>
            </comment>
            <comment>
         <value>5436</value>
            </comment>
            <comment>
         <value>123g</value>
            </comment>
          </comment-list>
    </b>
        </a>')
                    COLUMNS
                      id                  PATH 'id',
                      comment_list        XMLTYPE PATH 'comment-list'
                  ) t1
        ) t,
        XMLTABLE
        (
          '//comment'
          PASSING t.comment_list
          COLUMNS
            value  PATH 'value'
        ) c

but cartesian appears. Anyone has an idea how to achieve it in 1 SQL? My first idea is to do plsql loop with one xmltable, and then another loop in loop. It is easy, but loop in loop - across larger xml that would be not efficient.

anyone has an idea how to solve it in 1 SQL?

Regards

You already got the desired output if replace the asterisk at top with `id, value` ... — Barbaros Özhan, Jun 23 '23 at 08:23
Damn... in this mock XML its workin, but in my larger XML there is a cartesian, ID multiplies... — q4za4, Jun 23 '23 at 08:41

score 2 · Accepted Answer · answered Jun 23 '23 at 08:27

You can use XMLTABLE and, to get the id, you can go back up the element hierarchy using ..:

SELECT x.*
FROM   table_name t
       CROSS APPLY XMLTABLE(
         '/a/b/comment-list/comment'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           id NUMBER PATH './../../id',
           comm VARCHAR2(10) PATH './value'
         
       ) x;

Which, for the sample data:

CREATE TABLE table_name (xml) AS
SELECT '<a>
   <b>
      <id>1</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
  </b>
  <b>
  <id>2</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
</b>
    </a>' FROM DUAL;

Outputs:

ID	COMM
1	asd
1	23
1	5436
1	123g
2	asd
2	23
2	5436
2	123g

fiddle

Brilliant. Never thought that could be solved like this - path to list, and go back in columns by ./../. Thanks! — q4za4, Jun 23 '23 at 09:16

score 0 · Answer 2 · answered Jun 23 '23 at 08:45

0

Okey, my mistake. It actually works. In my xml which is 100k chars, i didn't notice that some id's appear more than - few nodes with same id.

This SQl works

answered Jun 23 '23 at 08:45

q4za4

630
4
12

1

Btw @MT0 's method is noticeably pretty good. – Barbaros Özhan Jun 23 '23 at 08:49

Oracle, XMLTABLE - extract data with list of values

2 Answers2