在Oracle中选择xml元素值 [英] Select xml element value in Oracle
问题描述
我正在尝试从位于Oracle表的XMLTYPE列中的xml元素中提取值.我尝试提取的xml元素有一个为其定义了名称空间的父级. xml看起来像:
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:
<a>
<b xmlns="urn:www.someSite.com/myModel">
<c>my value</c>
</b>
</a>
如果我要提取"a"元素的内容,则会正确返回其上下文:
If I want to extract the content of the "a" element, its context is correctly returned:
SELECT Extract(myColumn, '/a') FROM myTable;
但是对于返回"c"元素的内容,我没有成功找到任何可用的版本.以下说明不起作用:
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:
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?
推荐答案
由于a
元素没有名称空间,因此您可以先提取其子元素,而无需在函数中使用名称空间,然后从b
,其名称空间为:
试试:
Since the a
element 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 b
with the namespace:
Try:
select extract(extract(myColumn, 'a/*'),
'b/c/text()',
'xmlns=urn:www.someSite.com/myModel')
from myTable
这篇关于在Oracle中选择xml元素值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!