CODEWITHSUNDEEP
xmlselectdb2xquery
Hana Bzh
Hana Bzh

Reputation: 2260

xquery select on attributes

There is a table A with date column named logdate and xml column named tag which has xml value like this:

<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" />

I want to select records with specific logdate which has classid="1" in their xml.

example: TableName : TestTable Columns: logdate (Decimal), xml (Xml)

sample records:

1) logdate=20181101, xml=[something like what I wrote above]

2) logdate=20181101, xml=[something like what I wrote above]

3) logdate=20181102, xml=[something like what I wrote above]

4) logdate=20181103, xml=[something like what I wrote above]

5) logdate=20181103, xml=[something like what I wrote above]

Result I want:

className where Logdate>20181101 And classid=1

for example:

1) 20181102, testclass_record3

2) 20181103, testclass_record4

3) 20181103, testclass_record5

How can I use db2 xquery for this select?

Upvotes: 0

Views: 87

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

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;

Upvotes: 1

Related Questions