oracle 如何从 XMLType 节点中提取元素路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15896143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract element-path from XMLType Node?
提问by towi
I would like to have a select statement on an XML document and one column should return me the path of each node.
我想在 XML 文档上有一个 select 语句，并且一列应该返回每个节点的 路径。
For example, given the data
例如，给定数据
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;
Which results in
这导致
column_value
--------
<user><name>user1</name></user>
<user><name>user2</name></user>
<user><name>user3</name></user>
<user><name>user4</name></user>
I'd like to have a result like this:
我想要这样的结果：
path value
------------------------ --------------
/users/user/name user1
/users/user/name user2
/users/group/user/name user3
/users/user/name user4
I can not see how to get to this. I figure there are two thing that have to work together properly:
我看不出如何做到这一点。我认为有两件事必须一起正常工作：
- Can I extract the
pathfrom anXMLTypewith a single operation or method, or do I have to do this with string-magic? - What is the correct XPath expression so that I do get the whole element path(if thats possible), eg.
<users><group><user><name>user3</name></user></group></user>insead of<user><name>user3</name></user>?
- 我可以使用单个操作或方法
path从 an 中提取XMLType，还是必须使用字符串魔术来执行此操作？ - 什么是正确的 XPath 表达式，以便我获得整个元素路径（如果可能的话），例如。
<users><group><user><name>user3</name></user></group></user>代替<user><name>user3</name></user>?
Maybe I am not understanding XMLTypefully, yet. It could be I need a different approach, but I can not see it.
也许我还没有XMLType完全理解。可能是我需要不同的方法，但我看不到它。
Sidenotes:
旁注：
- In the final version the XML document will be coming from CLOBs of a table, not a static document.
- The
pathcolumn can of course also use dots or whatever and the initial slash is not the issue, any representation would do. - Also I would not mind if every inner node also gets a result row (possibly with
nullasvalue), not only the ones withtext()in it (which is what I am really interested in). - In the end I will need the tail elementof
pathseparate (always"name"in the example here, but this will vary later), i.e.('/users/groups/user', 'name', 'user3'), I can deal with that separately.
- 在最终版本中，XML 文档将来自表的 CLOB，而不是静态文档。
- 该
path列当然也可以使用点或其他任何东西，并且最初的斜杠不是问题，任何表示都可以。 - 此外，我不介意每个内部节点是否也获得一个结果行（可能带有
nullasvalue），而不仅仅是其中的那些text()（这是我真正感兴趣的）。 - 最后，我将需要尾部元件的
path单独的（总是"name"在这里的示例中，但是这将在后面有所不同），即('/users/groups/user', 'name', 'user3')，我可以分别处理这一点。
回答by ThinkJet
You can achieve that with help of XMLTablefunction from Oracle XML DB XQuery function set:
您可以借助Oracle XML DB XQuery 函数集中的XMLTable函数实现这一点：
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'
)
For me XQuery looks at least more intuitive for XML data manipulation than XSLT.
对我来说，XQuery 看起来至少比 XSLT 对 XML 数据操作更直观。
You can find useful set of XQuery functions here.
您可以在此处找到一组有用的 XQuery 函数。
Update 1
更新 1
I suppose that you need totally plain dataset with full data at last stage. This target can be reached by complicated way, constructed step-by-step below, but this variant is very resource-angry. I propose to review final target (selecting some specific records, count number of elements etc.) and after that simplify this solution or totally change it.
我想您在最后阶段需要具有完整数据的完全简单的数据集。这个目标可以通过复杂的方式达到，下面一步一步构建，但是这个变体非常耗费资源。我建议最终目标（选择一些特定的记录，计算元素数量等），然后简化这个解决方案或完全改变它。
Update 2
更新 2
All steps deleted from this Update except last because @A.B.Cade proposed more elegant solution in comments. This solution provided in Update 3section below.
从此更新中删除了除最后一步之外的所有步骤，因为@ABCade 在评论中提出了更优雅的解决方案。此解决方案在下面的更新 3部分中提供。
Step 1- Constructing dataset of id's with corresponding query results
第 1 步- 构建具有相应查询结果的 id 数据集
Step 2- Aggregating to single XML row
第 2 步- 聚合到单个 XML 行
Step 3- Finally get full plain dataset by querying constracted XML with XMLTable
第 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'
)
Update 3
更新 3
As mentioned by @A.B.Cade in his comment, there are really simple way to join ID's with XQuery results.
正如@ABCade 在他的评论中提到的，有非常简单的方法可以将 ID 与 XQuery 结果连接起来。
Because I don't like external links in answers, code below represents his SQL fiddle, a little bit adapted to the data source from this answer:
因为我不喜欢答案中的外部链接，下面的代码代表了他的 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
回答by A.B.Cade
This is not perfect but can be a start:
这并不完美，但可以作为一个开始：
with xslt as (
select '<?xml version="1.0" ?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<records>
<xsl:apply-templates/>
</records>
</xsl:template>
<xsl:template match="//name">
<columns>
<path>
<xsl:for-each select="ancestor-or-self::*">
<xsl:call-template name="print-step"/>
</xsl:for-each>
</path>
<value>
<xsl:value-of select="."/>
</value>
<xsl:apply-templates select="*"/>
</columns>
</xsl:template>
<xsl:template name="print-step">
<xsl:text>/</xsl:text>
<xsl:value-of select="name()"/>
<xsl:text>[</xsl:text>
<xsl:value-of select="1+count(preceding-sibling::*)"/>
<xsl:text>]</xsl:text>
</xsl:template>
</xsl:stylesheet>'
xsl from dual)
, xmldata as
(select xmltransform(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>'), xmltype(xsl)) xd from xslt)
select XT.*
from xmldata c,
xmltable('$x//columns' passing c.xd
as "x"
columns
path_c VARCHAR2(4000) PATH 'path',
value_c VARCHAR2(4000) PATH 'value'
) as XT
This is what I tried to do:
这就是我试图做的：
Since you want the "path" I had to use xslt (credits to this post)
既然你想要“路径”，我不得不使用 xslt（这篇文章的学分）
Then I used xmltransformfor transforming your original xml with the xsl to the desired output (path, value)
然后我使用xmltransform将带有 xsl 的原始 xml 转换为所需的输出（路径、值）
Then I used xmltableto read it as a table
然后我习惯xmltable把它当成一张表来读
回答by user1438593
This improves on above answer by A.B.Cade:
这改进了 ABCade 的上述答案：
<xsl:template name="print-step">
<xsl:variable name="name" select="name()" />
<xsl:text>/</xsl:text>
<xsl:value-of select="$name"/>
<xsl:text>[</xsl:text>
<xsl:value-of select="1+count(preceding-sibling::*[name()=$name])"/>
<xsl:text>]</xsl:text>
</xsl:template>
With result:
结果：
/users[1]/user[1]/name[1] user1
/users[1]/user[1]/name[1] user1
/users[1]/user[2]/name[1] user2
/users[1]/user[2]/name[1] user2
/users[1]/group[1]/user[1]/name[1] user3
/users[1]/group[1]/user[1]/name[1] user3
/users[1]/user[3]/name[1] user4
/users[1]/user[3]/name[1] user4
Instead of:
代替：
/users[1]/user[1]/name[1] user1
/users[1]/user[1]/name[1] user1
/users[1]/user[2]/name[1] user2
/users[1]/user[2]/name[1] user2
/users[1]/group[3]/user[1]/name[1] user3
/users[1]/group[3]/user[1]/name[1] user3
/users[1]/user[4]/name[1] user4
/users[1]/user[4]/name[1] user4
