I have a XML structure as below:
<root>
<firstChild>
<a>
<a1>someText</a1>
<a2>someNumber</a2>
<a>
<a>
<a1>someText1</a1>
<a2>someNumber1</a2>
<a>
<a>
<a1>someText2</a1>
<a2>someNumber2</a2>
<a>
<a>
<a1>someText3</a1>
<a2>someNumber3</a2>
<a>
</firstChild>
</root>
I want to write a DB2 SQL which will return all application id which have a1 as someText1 and a2 as someNumber1.
For more information I have a table say APPLICATION which has application_xml as column. This column has all the xml documents as shown above and are stored against each application id.
Can someone please suggest.
I have tried below query but it did not succeed.
select XMLQUERY('copy $new := $application_xml
for $i in $new/root/firstChild/a[a1 = "someText1"], $new/root/firstChild/a[a2 = "someNumber1"]
return $new') from application