問題描述
我想要一個(gè)關(guān)于 XML 文檔的選擇語句，并且一列應(yīng)該返回每個(gè)節(jié)點(diǎn)的路徑.
I would like to have a select statement on an XML document and one column should return me the path of each node.
例如，給定數(shù)據(jù)
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;
結(jié)果
column_value
--------
<user><name>user1</name></user>
<user><name>user2</name></user>
<user><name>user3</name></user>
<user><name>user4</name></user>
我想要這樣的結(jié)果:
path value
------------------------ --------------
/users/user/name user1
/users/user/name user2
/users/group/user/name user3
/users/user/name user4
我不知道如何做到這一點(diǎn).我認(rèn)為有兩件事必須正確協(xié)同工作:
I can not see how to get to this. I figure there are two thing that have to work together properly:
- 我可以使用單個(gè)操作或方法從
XMLType中提取path嗎，或者我必須使用 string-magic 來執(zhí)行此操作嗎?? - 什么是正確的 XPath 表達(dá)式，以便我獲得整個(gè)元素路徑(如果可能的話)，例如.
<users><group><user><name>user3</name></user></group></user>在<;用戶>?user3
- 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>?
也許我還沒有完全理解 XMLType.可能是我需要不同的方法，但我看不到.
Maybe I am not understanding XMLType fully, yet. It could be I need a different approach, but I can not see it.
旁注:
- 在最終版本中，XML 文檔將來自表的 CLOB，而不是靜態(tài)文檔.
path列當(dāng)然也可以使用點(diǎn)或其他任何東西，并且最初的斜杠不是問題，任何表示都可以.- 此外，我不介意每個(gè)內(nèi)部節(jié)點(diǎn)是否也獲得一個(gè)結(jié)果行(可能將
null作為value)，而不僅僅是帶有text() 的那些在其中(這是我真正感興趣的). - 最后，我需要將
path的 tail 元素 分開(在示例中總是"name"，但這會(huì)有所不同稍后)，即('/users/groups/user', 'name', 'user3')，我可以單獨(dú)處理.
- 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 element of
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.
推薦答案
您可以在 XMLTable 函數(shù)來自 Oracle XML DB XQuery 函數(shù)集:
You can achieve that with help of XMLTable function from 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 數(shù)據(jù)操作更直觀.
For me XQuery looks at least more intuitive for XML data manipulation than XSLT.
您可以在此處找到有用的 XQuery 函數(shù)集.
You can find useful set of XQuery functions here.
更新 1
我想您在最后階段需要具有完整數(shù)據(jù)的完全簡單的數(shù)據(jù)集.這個(gè)目標(biāo)可以通過復(fù)雜的方式達(dá)到，下面一步一步構(gòu)建，但是這個(gè)變體非常耗費(fèi)資源.我建議審查最終目標(biāo)(選擇一些特定的記錄，計(jì)算元素?cái)?shù)量等)，然后簡化此解決方案或完全更改它.
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.
更新 2
除了最后一步之外，所有步驟都從此更新中刪除，因?yàn)锧A.B.Cade 在評(píng)論中提出了更優(yōu)雅的解決方案.此解決方案在下面的更新 3 部分中提供.
All steps deleted from this Update except last because @A.B.Cade proposed more elegant solution in comments. This solution provided in Update 3 section below.
Step 1 - 構(gòu)建帶有對應(yīng)查詢結(jié)果的 id 數(shù)據(jù)集
Step 1 - Constructing dataset of id's with corresponding query results
第 2 步 - 聚合到單個(gè) XML 行
Step 2 - Aggregating to single XML row
第 3 步 - 最后通過使用 XMLTable 查詢壓縮的 XML 獲得完整的普通數(shù)據(jù)集
Step 3 - Finally get full plain dataset by querying constracted XML with XMLTable
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 在他的評(píng)論中提到的，有非常簡單的方法可以將 ID 與 XQuery 結(jié)果連接起來.
As mentioned by @A.B.Cade in his comment, there are really simple way to join ID's with XQuery results.
因?yàn)槲也幌矚g答案中的外部鏈接，下面的代碼代表他的 SQL 小提琴，有點(diǎn)適應(yīng)這個(gè)答案的數(shù)據(jù)源:
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:
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
這篇關(guān)于如何從 XMLType 節(jié)點(diǎn)中提取元素路徑?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！