我在Oracle XDB中有一张名为test的表,其中有一行。
SQL> select * from test;
SYS_NC_ROWINFO$
---------------------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="">
<location>
<joiningDate id="onsite">
<hireDate>2012-06-18</hireDate>
</joiningDate>
<joiningDate id="offshore">
<hireDate>2011-07-8</hireDate>
</joiningDate>
</location>
</Employee>
播放时间:00:00:00.00
我也有类似的索引
CREATE INDEX test_xml_index ON test (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE test_dates_tab');
BEGIN
DBMS_XMLINDEX.registerParameter(
'myprop',
'ADD_GROUP GROUP test_dates
XMLTable test_dates_tab ''/Employee''
XMLNAMESPACES (''http://www.w3.org/2001/XMLSchema-instance'' AS "xsi")
COLUMNS onsite date PATH ''.//*[@id="onsite"]/hireDate/text()''
');
END;
/
为什么我的查询未返回空值?
SQL> select x.*
2 from test t,
3 xmltable(
4 xmlnamespaces(
5 'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
6 ),
7 'for $d in /Employee/location
8 where $d/joiningDate[@id="onsite"]/hireDate/text() = xs:date("2012-06-18")
9 return $d'
10 passing t.object_value
11 columns
12 hiredate date path 'hireDate'
13 ) as x
14
SQL> /
HIREDATE
--------------------
在您的帮助下得到了Jens
select x.*
from test t,
xmltable(
xmlnamespaces(
'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
),
'for $d in /Employee/location
where $d/joiningDate[@id="onsite"]/hireDate/text() = xs:date("2012-06-18")
return $d'
passing t.object_value
columns
hiredate date path 'joiningDate[@id="onsite"]/hireDate'
) as x
XQuery中有一个输入错误:元素被命名<hireDate/>
,但是您的轴步长为hiredate
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句