xquery选择属性

Question

有一个表A，其日期列名为logdate，xml列名为tag，其xml值如下：

<process id="1540193803286" startTime="2018-10-24 10:01:26.467" >
<user>
<userId>2020</userId>   
</user> 
<executionCclass>
<executionNode classid="1" className="testclass_record1" methodName="testmethod" timeSpent="0" />
<executionNode classid="1-1" className="testclass2_record1" methodName="testmethod2" timeSpent="0" />

我想选择具有特定logdate的记录，该记录的xml中有classid="1"。

示例：表格名称：测试表格列：logdate(十进制(、xml(xml(

样本记录：

1( logdate=20181101，xml=[类似于我上面写的内容]

2( logdate=20181101，xml=[类似于我上面写的内容]

3( logdate=20181102，xml=[类似于我上面写的内容]

4( logdate=20181103，xml=[类似于我上面写的内容]

5( logdate=20181103，xml=[类似于我上面写的内容]

我想要的结果：

className其中Logdate>20181101并且classid=1

例如：

1( 20181102，testclass_record3

2( 20181103，testclass_record4

3( 20181103，testclass_record5

如何使用db2xquery进行此选择？

Answer 1

试试这个：

declare global temporary table session.test_xml (logdate dec(8), tag xml) with replace on commit preserve rows not logged;
insert into session.test_xml values 
(20181102, xmlparse (document '
<process id="1540193803286" startTime="2018-10-24 10:01:26.467" >
<user>
<userId>2020</userId>   
</user> 
<executionCclass>
<executionNode classid="1" className="testclass_record1" methodName="testmethod" timeSpent="0" />
<executionNode classid="1-1" className="testclass2_record1" methodName="testmethod2" timeSpent="0" />
</executionCclass>
</process>
'
))
, (20181102, xmlparse (document '
<process id="1540193803286" startTime="2018-10-24 10:01:26.467" >
<user>
<userId>2020</userId>   
</user> 
<executionCclass>
<executionNode classid="2" className="testclass_record1" methodName="testmethod" timeSpent="0" />
<executionNode classid="1-1" className="testclass2_record1" methodName="testmethod2" timeSpent="0" />
</executionCclass>
</process>
'
))
;
select a.logdate, t.classname
from session.test_xml a
, xmltable ('$doc/process/executionCclass/executionNode[@classid="1"]' passing a.tag as "doc" columns
classname varchar(128) path '@className'
) t 
where xmlexists('$doc/process/executionCclass/executionNode[@classid="1"]' passing a.tag as "doc")
and Logdate>20181101;