xml - PLSQL XMLTable XPath 获取最深深度的所有标签

Question

在PL/SQL中有没有办法获取最深深度的所有节点？

示例1:

<responseObject>
    <response>
        <value1>A</value1>
        <value2>B</value2>
        <error>A11</error>
    <response>
<responseObject>

示例2:

<responseObject>
    <response1>A</response1>
    <response2>B</response2>
    <response4>D</response4>
    <random>1</random>
<responseObject>

在示例 1 中，我想获取 value1、value2 和错误。在示例 2 中，我想获得response1、response2、response4 和随机数。目前我的 XPath 设置为 '/responseObject/*/*' 但这不适用于第二种情况。

示例代码:

DECLARE
  lxml xmltype;
begin
  lxml := 
  xmltype('<responseObject>
              <response>
                  <value1>A</value1>
                  <value2>B</value2>
                  <error>A11</error>
              <response>
           <responseObject>');

  FOR test IN (
    select tag,
           VALUE
    FROM   xmltable('responseObject/*/*'
             passing lxml
             columns
               tag VARCHAR2(128) path 'name()',
               VALUE VARCHAR2(128) path '.'
           ) t
  )
  LOOP
    dbms_output.put_line(test.tag || ' - ' || test.value);
  END LOOP;
end;

Answer 1

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE table_name ( id, xml ) AS
SELECT 1, '<responseObject>
    <response>
        <value1>A</value1>
        <value2>B</value2>
        <error>A11</error>
    </response>
</responseObject>' FROM DUAL UNION ALL
SELECT 2, '<responseObject>
    <response1>A</response1>
    <response2>B</response2>
    <response4>D</response4>
    <random>1</random>
</responseObject>' FROM DUAL;

查询 1:

SELECT id, x.*
FROM   TABLE_NAME t
       CROSS JOIN
       xmltable(
         '/responseObject//*[last()][not(*)]'
         passing XMLType( t.xml )
         COLUMNS
           tag   VARCHAR2(128) path 'name()',
           value VARCHAR2(128) path '.'
       ) x

<强> Results :

| ID |    TAG | VALUE |
|----|--------|-------|
|  1 |  error |   A11 |
|  2 | random |     1 |