在 Oracle 中选择 xml 元素值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9975518/
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
Select xml element value in Oracle
提问by axl g
I am trying to extract a value from an xml element, located in an XMLTYPE column in an Oracle Table. The xml element which I am trying to extract have a parent for which a namespace is defined. The xml looks something like:
我正在尝试从位于 Oracle 表的 XMLTYPE 列中的 xml 元素中提取值。我试图提取的 xml 元素有一个为其定义了命名空间的父元素。xml 看起来像：
<a>
<b xmlns="urn:www.someSite.com/myModel">
<c>my value</c>
</b>
</a>
If I want to extract the content of the "a" element, its context is correctly returned:
如果我想提取“a”元素的内容，则正确返回其上下文：
SELECT Extract(myColumn, '/a') FROM myTable;
But for returning the content of the "c" element I didn't succeed to find any version to work. The following instructions does not work:
但是为了返回“c”元素的内容，我没有成功找到任何可以工作的版本。以下说明不起作用：
SELECT Extract(myColumn, '/a/b/c') FROM myTable;
SELECT Extract(myColumn, '/a/b/c', 'xmlns="urn:www.someSite.com/myModel"') FROM myTable;
SELECT Extract(myColumn, '/a/b/c', 'urn:www.someSite.com/myModel') FROM myTable;
Can anybody help me, with the extract statement that would work in this case?
任何人都可以使用在这种情况下有效的提取语句来帮助我吗？
回答by Jokke Heikkil?
select a.*
from XMLTABLE(
XMLNAMESPACES('urn:www.someSite.com/myModel' AS "ns"),
'/*'
PASSING my.myColumn
COLUMNS
val VARCHAR2(2000) PATH '/a/ns:b/ns:c'
) a, myTable my;
回答by A.B.Cade
Since the aelement does not have the namespace, you can first extract its child elements without using namespaces in the function, and then extract the value from the bwith the namespace:
Try:
由于a元素没有命名空间，可以先在函数中不使用命名空间的情况下提取其子元素，然后从b具有命名空间的中提取值：
尝试：
select extract(extract(myColumn, 'a/*'),
'b/c/text()',
'xmlns=urn:www.someSite.com/myModel')
from myTable
回答by Miguel Angel Maroto
When the default namespace changes, one way to specify namespaces is to write the wildcard character ('*') and the local-name()and namespace-uri()XPath functions.
当默认名称空间更改时，指定名称空间的一种方法是编写通配符 ('*') 以及local-name()和namespace-uri()XPath 函数。
select extract(myColumn, '/a/*[local-name()='b' and namespace-uri()='urn:www.someSite.com/myModel']/*[local-name()='c' and namespace-uri()='urn:www.someSite.com/myModel']')
from myTable
select extract(myColumn, '/a/*[local-name()='b' and namespace-uri()='urn:www.someSite.com/myModel']/*[local-name()='c' and namespace-uri()='urn:www.someSite.com/myModel']')
from myTable
回答by sony
SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result"
FROM emp
