Filtering XML using multiple conditions of xquery

Question

Hi I have many XML files which have the structure like:

<mydoc>
      <EC>
         <spec>
            <name>A para</name>
            <para>A</para>
            <val>40</val>
         </spec>
         <spec>
            <name>Input Voltage</name>
            <para>Vin</para>
            <val>40</val>
         </spec>
      </EC>
</mydoc>

I need to filter out only those XML files which has a spec with para=Vin and val>30

What should be my Xquery like? In DB2 I have this so far?

for $x in db2-fn:xmlcolumn('TABLE.XMLCOLUMN')/mydoc

So this would iterate over all XML files and I will have mydoc element tag in $x but now how do I implement the multiple conditions and return just the name of the spec which satisfies my conditions?

http://amolnpujari.wordpress.com/2008/04/12/db2-native-xml/ and , http://amolnpujari.wordpress.com/2008/04/07/sqlxml-vs-xquery/ might help — Amol Pujari, Sep 25 '14 at 04:27

wst · Accepted Answer · 2014-08-18T18:19:49.037

1

There are a couple ways to express this. XPath predicates:

for $x in db2-fn:xmlcolumn('TABLE.XMLCOLUMN')/mydoc[EC/spec[para = 'Vin' and val > 40]]

Or XQuery's where clause. This also uses XPath predicates and I think it is slightly easier to read:

for $x in db2-fn:xmlcolumn('TABLE.XMLCOLUMN')/mydoc
let $specs := $x/EC/spec
where ($spec[para = 'Vin' and val > 40])

Both should give you the same results.

edited Aug 18 '14 at 18:19

answered Aug 18 '14 at 18:02

wst

11,681
1
24
39

Thank you for the reply. So this for would iterate over all documents and for each document it iterates over all specs? But I just need 1 return for each document i.e. only if the document satisfies the conditions. How do I do that? – Milind Aug 18 '14 at 18:07
@Milind So you want to return a single if any of its descendants meet your criteria? Or only if all of the s meet the criteria? – wst Aug 18 '14 at 18:13
I want to return a single mydoc if any of its spec descendants meet my criteria. Thanks. – Milind Aug 18 '14 at 18:14
@Milind Updated my answer with new logic that should answer your question. – wst Aug 18 '14 at 18:20

Filtering XML using multiple conditions of xquery

1 Answers1