在 Oracle 中选择 xml 元素值

2022-01-14 00:00:00 xml extract namespaces 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

相关文章