sql - 如何从 XMLType 节点中提取元素路径？

Question

我想在 XML 文档上有一个 select 语句，其中一列应该返回我的每个节点的路径。

例如给定数据

SELECT * 
FROM TABLE(XMLSequence(
  XMLTYPE('<?xml version="1.0"?>
    <users><user><name>user1</name></user>
           <user><name>user2</name></user>
           <group>
              <user><name>user3</name></user>
           </group>
           <user><name>user4</name></user>
    </users>').extract('/*//*[text()]'))) t;

结果

column_value
--------
<user><name>user1</name></user>
<user><name>user2</name></user>
<user><name>user3</name></user>
<user><name>user4</name></user>

我想要这样的结果:

path                     value
------------------------ --------------
/users/user/name         user1
/users/user/name         user2
/users/group/user/name   user3
/users/user/name         user4

我不知道如何做到这一点。我认为有两件事必须一起正常工作:

• 我可以提取 path 吗？来自 XMLType使用单个操作或方法，还是必须使用字符串魔术来完成？
• 什么是正确的 XPath 表达式，这样我才能得到整个元素路径(如果可能的话)，例如。 <users><group><user><name>user3</name></user></group></user>工商管理学院 <user><name>user3</name></user> ？

可能是我没看懂XMLType完全，还。可能是我需要一种不同的方法，但我看不到它。

旁注:

• 在最终版本中，XML 文档将来自表的 CLOB，而不是静态文档。
• path column 当然也可以使用点或其他任何东西，初始斜杠不是问题，任何表示都可以。
• 此外，我不介意每个内部节点是否也得到结果行(可能 null 为 value )，而不仅仅是 text() 的结果行在其中(这是我真正感兴趣的)。
• 最后我需要path 的tail 元素分开(在此处的示例中始终为 "name"，但稍后会有所不同)，即 ('/users/groups/user', 'name', 'user3') ，我可以单独处理。

Answer 1

您可以在 XMLTable 的帮助下实现这一目标来自 Oracle XML DB XQuery function set 的功能:

select * from 
  XMLTable(
    '
     declare function local:path-to-node( $nodes as node()* )  as xs:string* {
       $nodes/string-join(ancestor-or-self::*/name(.), ''/'')
     };
     for $i in $rdoc//name 
       return <ret><name_path>{local:path-to-node($i)}</name_path>{$i}</ret>
    '
    passing 
    XMLParse(content '
      <users><user><name>user1</name></user>
           <user><name>user2</name></user>
           <group>
              <user><name>user3</name></user>
           </group>
           <user><name>user4</name></user>
      </users>'
    )
    as "rdoc"
    columns 
      name_path  varchar2(4000) path '//ret/name_path',
      name_value varchar2(4000) path '//ret/name'

  )

对我来说，XQuery 至少比 XSLT 更直观地处理 XML 数据。

您可以找到有用的 XQuery 函数集 here .

更新 1

我想您在最后阶段需要具有完整数据的完全普通的数据集。这个目标可以通过下面逐步构建的复杂方式来实现，但是这个变体非常耗费资源。我建议审查最终目标(选择一些特定的记录，计算元素的数量等)，然后简化这个解决方案或完全改变它。

更新 2

除最后一个步骤外，此更新中删除了所有步骤，因为@A.B.Cade 在评论中提出了更优雅的解决方案。此解决方案在下面的更新 3 部分中提供。

第 1 步 - 构建具有相应查询结果的 id 数据集

第 2 步 - 聚合到单个 XML 行

第 3 步 - 通过使用 XMLTable 查询构造的 XML，最终获得完整的普通数据集

with xmlsource as (
  -- only for purpose to write long string only once
  select '
      <users><user><name>user1</name></user>
           <user><name>user2</name></user>
           <group>
              <user><name>user3</name></user>
           </group>
           <user><name>user4</name></user>
      </users>' xml_string
   from dual   
),
xml_table as ( 
  -- model of xmltable
  select 10 id, xml_string xml_data from xmlsource union all 
  select 20 id, xml_string xml_data from xmlsource union all 
  select 30 id, xml_string xml_data from xmlsource 
) 
select  *
from
  XMLTable(
    '
        for $entry_user in $full_doc/full_list/list_entry/name_info
          return <tuple>
                   <id>{data($entry_user/../@id_value)}</id>
                   <path>{$entry_user/name_path/text()}</path>
                   <name>{$entry_user/name_value/text()}</name>
                  </tuple> 
    '
    passing ( 
      select  
        XMLElement("full_list", 
          XMLAgg(     
            XMLElement("list_entry",
              XMLAttributes(id as "id_value"),
              XMLQuery(
                '
                 declare function local:path-to-node( $nodes as node()* )  as xs:string* {
                   $nodes/string-join(ancestor-or-self::*/name(.), ''/'')
                 };(: function to construct path :) 
                 for $i in $rdoc//name return <name_info><name_path>{local:path-to-node($i)}</name_path><name_value>{$i/text()}</name_value></name_info>
                '
                passing by value XMLParse(content xml_data) as "rdoc"
                returning content
              )
            )
          )
        )        
        from xml_table
    )   
    as "full_doc"      
    columns
      id_val   varchar2(4000) path '//tuple/id',
      path_val varchar2(4000) path '//tuple/path',
      name_val varchar2(4000) path '//tuple/name'
  )    

更新 3

@A.B.Cade 在他的评论中提到，有非常简单的方法可以将 ID 与 XQuery 结果结合起来。

因为我不喜欢答案中的外部链接，下面的代码代表his SQL fiddle ，稍微适应了这个答案的数据源:

with xmlsource as (
  -- only for purpose to write long string only once
  select '
      <users><user><name>user1</name></user>
           <user><name>user2</name></user>
           <group>
              <user><name>user3</name></user>
           </group>
           <user><name>user4</name></user>
      </users>' xml_string
   from dual   
),
xml_table as ( 
  -- model of xmltable
  select 10 id, xml_string xml_data from xmlsource union all 
  select 20 id, xml_string xml_data from xmlsource union all
  select 30 id, xml_string xml_data from xmlsource
)
select xd.id, x.*  from
xml_table xd,
  XMLTable(
    'declare function local:path-to-node( $nodes as node()* )  as xs:string* {$nodes/string-join(ancestor-or-self::*/name(.), ''/'')     };     for $i in $rdoc//name        return <ret><name_path>{local:path-to-node($i)}</name_path>{$i}</ret>    '
    passing
    XMLParse(content xd.xml_data
    )
    as "rdoc"
    columns
      name_path  varchar2(4000) path '//ret/name_path',
      name_value varchar2(4000) path '//ret/name'

  ) x