如何从 Oracle XMLTYPE 中提取叶节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/611680/
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 leaf nodes from Oracle XMLTYPE
提问by kurosch
I want to extract only the leaf nodes from an XMLTYPE object in Oracle 10g
我只想从 Oracle 10g 中的 XMLTYPE 对象中提取叶节点
SELECT
t.getStringVal() AS text
FROM
TABLE( XMLSequence(
XMLTYPE(
'<xml>
<node>
<one>text</one>
</node>
<node>
<two>text</two>
</node>
<node>
<three>text</three>
</node>
</xml>'
).extract( '//*' )
) ) t
What should I use as the WHERE clause so this returns only these:
我应该使用什么作为 WHERE 子句，以便只返回这些：
<one>text</one>
<two>text</two>
<three>text</three>
I've tried the following but they don't work:
我已经尝试了以下但它们不起作用：
WHERE t.existsNode( '//*' ) = 0
WHERE t.existsNode( '/.//*' ) = 0
WHERE t.existsNode( './/*' ) = 0
What am I missing?
我错过了什么？
采纳答案by kurosch
Nevermind, I found it:
没关系，我找到了：
WHERE
t.existsNode( '/*//*' ) = 0
回答by MT0
The XPath //*[not(*)]will get any element at level 1 or deeper that does not have any children:
XPath//*[not(*)]将获取任何级别 1 或更深的没有任何子项的元素：
Query 1:
查询 1：
SELECT t.getStringVal()
FROM TABLE(
XMLSEQUENCE(
XMLTYPE(
'<xml>
<node><one>text</one></node>
<node><two>text</two></node>
<node><three>text</three></node>
</xml>'
).extract( '//*[not(*)]' )
)
) t
结果：
| T.GETSTRINGVAL() |
|---------------------|
| <one>text</one> |
| <two>text</two> |
| <three>text</three> |
If you want to be able to include the root element then use the XPath (//*|/*)[not(*)]
如果您希望能够包含根元素，请使用 XPath (//*|/*)[not(*)]
回答by Mohit Tamrakar
You can try the following PL/SQL:
您可以尝试以下 PL/SQL：
DECLARE
x XMLType := XMLType(
'<?xml version="1.0" ?>
<xml>
<node>
<one>text</one>
</node>
<node>
<two>text</two>
</node
<node>
<three>text</three>
</node>
</xml>');
BEGIN
FOR r IN (
SELECT ExtractValue(Value(p),'/XML/node/one/text()') as one_x
,ExtractValue(Value(p),'/XML/node/two/text()') as TWO_x
,ExtractValue(Value(p),'/XML/node/three/text()') as three
FROM TABLE(XMLSequence(Extract(x,'/XML/node/'))) p
) LOOP
/* do whatever you want with r.one_x, r.TWO_x, r.three_x */
END LOOP;
END;
