如何用单条Oracle SQL结合XMLTABLE提取嵌套XML关联数据？

示例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>

期望输出 #

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

尝试的SQL及问题 #

我用XMLTABLE编写了如下SQL：

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

执行后出现笛卡尔积问题。原本考虑用PL/SQL嵌套循环实现，但大数据量下效率太低，寻求单条SQL解决方案。

方案一：嵌套XMLTABLE精准关联节点 #

SELECT b.id, c.comment_value
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 NUMBER PATH 'id',
         comments XMLTYPE PATH 'comment-list/comment'
     ) b,
     XMLTABLE(
       '.'
       PASSING b.comments
       COLUMNS
         comment_value VARCHAR2(100) PATH 'value'
     ) c;

• 外层XMLTABLE遍历每个<b>节点，提取id和单个<comment>节点（而非整个comment-list）
• 内层XMLTABLE直接处理当前<comment>节点，提取value值
• 路径精准匹配，避免笛卡尔积，且解析效率更高

方案二：单XMLTABLE直接定位关联 #

SELECT 
  x.id,
  x.comment_value
FROM XMLTABLE(
       '/a/b/comment-list/comment'
       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 NUMBER PATH '../../id',
         comment_value VARCHAR2(100) PATH 'value'
     ) x;

• 直接定位到每个<comment>节点，通过../../id向上两级找到对应<b>节点的id值
• 单次解析完成，逻辑更简洁，大数据量下性能最优

内容的提问来源于stack exchange，提问作者q4za4