CODEWITHSUNDEEP
sqldb2xquery-sql
Sam
Sam

Reputation: 2442

How to write Select query for selecting particular xml nodes in DB2 which occur multiple times?

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

Upvotes: 0

Views: 1213

Answers (1)

data_henrik
data_henrik

Reputation: 17118

Based on your description I assume that the table has two columns application id (aid) and application_xml. As you want to return the application id the base structure of the query is

select aid from application

Now we need the condition of which rows qualify. You state that in the related XML document the elements a1 and a2 need to have a certain value. The function xmlexists is the one to use in the WHERE clause of SQL:

select aid from application
where xmlexists('$d/root/firstChild/a[a1 = "someText1" and a2 = "someNumber1"]' passing application_xml as "d")

The XMLEXISTS is used as filtering predicate. The "passing" clause tells DB2 to expect "application_xml" under the name "d" inside the XPath/XQuery expression. The XPath expression itself is looking for the path /root/firstChild/a and under a specific "a" both the condition for "a1" and "a2" need to be true. If you want a broader condition, there would be also ways to express that.

Upvotes: 1

Related Questions