如何获取Oracle XPath表达式中父元素的名称? [英] How to get the name of the parent element in an Oracle XPath expression?
问题描述
我有一个查询,该查询从我的Clob中提取了一些XML节点.
I have a query that extract me some the XML nodes from my clob.
select pid, name, xml from (
select d.pid, d.name
, EXTRACT(xmltype(d.data), '//ns1:myId', 'xmlns:ns1="http://acme.com/') xml
from DATA d
order by d.pid desc
)
;
但是我想看看提取的xmlnode的父元素名称实际上是什么.我尝试过
But I'd like to see what the parent elements name of the extracted xmlnode actually is. I tried
, EXTRACT(xmltype(d.data), '//ns1:myId/../name()', ...) xml
和
, EXTRACT(xmltype(d.data), '//ns1:myId/name()', ...) xml
和
, EXTRACT(xmltype(d.data), '//ns1:myId/local-name()', ...) xml
但是Oracle拒绝所有带有无效令牌"错误消息的消息.
but Oracle rejects all f them with "invalid token" error messages.
我的Oracle版本是"11.2.0.3.0".
My Oracle Version is "11.2.0.3.0".
推荐答案
通过提取,您可以向上查找路径,但是(如MOS文档301709.1所述;它适用于9i,但除显示的错误外仍有效) :
Extract lets you look higher up the path, but (as noted in MOS document 301709.1; which is for 9i but still seems valid, apart from the error shown):
无法使用XPATH函数name()返回元素名称,因为方法extract()和extractValue()当前仅支持返回节点集的XPATH操作.
Using the XPATH function name() to return the element name is not possible, because the methods extract() and extractValue() currently only support XPATH operations returning a node-set.
因此,您不能在当前或父节点上使用name()
,local-name()
等函数.该文档中提供了一种变通方法,可以将其示例稍微简化为:
So you can't use the name()
, local-name()
etc. functions, on the current or parent node. There is a workaround of sorts in that document, which can be simplified slightly rom their example to:
EXTRACT(xmltype(d.data), '//ns1:myId/..',
'xmlns:ns1="http://acme.com/').getRootElement() xml2
或以稍微不同的形式:
xmltype(d.data).extract('//ns1:myId/..',
'xmlns:ns1="http://acme.com/').getRootElement()
两者都显示的演示
with data (pid, name, data) as (
select 42, 'Test', '<?xml version="1.0" encoding="ISO-8859-1"?>
<ns1:root xmlns:ns1="http://acme.com/"><ns1:parent><ns1:myId>1234</ns1:myId></ns1:parent></ns1:root>' from dual
)
select d.pid, d.name
, EXTRACT(xmltype(d.data), '//ns1:myId', 'xmlns:ns1="http://acme.com/') xml
, EXTRACT(xmltype(d.data), '//ns1:myId/..', 'xmlns:ns1="http://acme.com/').getRootElement() xml2
, xmltype(d.data).extract('//ns1:myId/..', 'xmlns:ns1="http://acme.com/').getRootElement() xml3
from DATA d
order by d.pid desc
;
PID NAME XML XML2 XML3
---------- ---- ------------------------------ ---------- ----------
42 Test <ns1:myId xmlns:ns1="http://ac parent parent
me.com/">1234</ns1:myId>
但是,不建议使用 extract()
一个>.您可以使用 XMLTable :
with data (pid, name, data) as (
select 42, 'Test', '<?xml version="1.0" encoding="ISO-8859-1"?>
<ns1:root xmlns:ns1="http://acme.com/"><parent><myId>1234</myId></parent></ns1:root>' from dual
)
select d.pid, d.name, x.*
from data d
cross join xmltable(xmlnamespaces('http://acme.com/' as "ns1"),
'/ns1:*//myId'
passing xmltype(d.data)
columns myId number path '.',
parent varchar2(20) path './../local-name()'
) x
order by d.pid desc;
PID NAME MYID PARENT
---------- ---- ---------- --------------------
42 Test 1234 parent
如果您需要更复杂的东西,则可以从节点级别中获取所需的任何内容,如该答案所示 ;但是根据您的说法,这太过分了.
If you need something more complicated you can pull whatever you need from the node levels, as shown in this answer; but from what you've said that's overkill here.
这篇关于如何获取Oracle XPath表达式中父元素的名称?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!