How to get data from two XML file using Xquery?

Question

Sample1.xml

<data><row><id>949459</id><product_id>4119945117</product_id></row>
    <row><id>781351</id><product_id>1009460692</product_id></row>
    <row><id>780163</id><product_id>1009755673</product_id></row>
    <row><id>1017226</id><product_id>1013393868</product_id></row>
    <row><id>1017956</id><product_id>1013393871</product_id></row>
    <row><id>1017310</id><product_id>1013393874</product_id></row>
    <row><id>771708</id><product_id>4388803569</product_id></row>
    <row><id>3270790</id><product_id>1013679270</product_id></row>
    <row><id>775869</id><product_id>1014142699</product_id></row>
    <row><id>1017599</id><product_id>1021870484</product_id></row>
    <row><id>1018789</id><product_id>1021870489</product_id></row>
    <row><id>1017091</id><product_id>1021870491</product_id></row>
    <row><id>1017333</id><product_id>1021870492</product_id></row>
    <row><id>1017630</id><product_id>1021870493</product_id></row>
    <row><id>1017774</id><product_id>1021870495</product_id></row>
    <row><id>1018192</id><product_id>1021870496</product_id></row>
    <row><id>1018725</id><product_id>4408034849</product_id></row>
    <row><id>1017990</id><product_id>1021870498</product_id></row>
    <row><id>1018027</id><product_id>1021870499</product_id></row>
    <row><id>1017166</id><product_id>1021870500</product_id></row>
    <row><id>769120</id><product_id>1032140806</product_id></row>
    <row><id>950336</id><product_id>1035310069</product_id></row>
    </data>

sample2.xml

<productSet>
 <row><product>4388803569</product></row>
 <row><product>4408034289</product></row>
 <row><product>4408034589</product></row>
 <row><product>4408034849</product></row>
 <row><product>4094557957</product></row>
 <row><product>4119945117</product></row>
</productSet>

Here is my Xquery to return all product element values from sample1 XML file by comparing with product_id element from sample 2 file. Here I am trying to retrieve all the not available product.

<outProduct_10310>
{ for $b in doc("sample1.xml")/data/row,
      $a in doc("sample2.xml")/productSet/row[product != $b/product_id]
  return   
      <op_id> { $b/product_id/text() } </op_id>
  }  </outProduct_10310>

This code will return correct matched data when I compare these two XML with equal symbol but I want to retrieve the unmatched data, But if I run above code it gives following output:

<outProduct_10310>
  <op_id>4119945117</op_id>
  <op_id>4119945117</op_id>
  <op_id>4119945117</op_id>
  <op_id>4119945117</op_id>
  <op_id>4119945117</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009460692</op_id>
  <op_id>1009755673</op_id>
  <op_id>1009755673</op_id>
  <op_id>1009755673</op_id>
  <op_id>1009755673</op_id>
  <op_id>1009755673</op_id>
  <op_id>1009755673</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393868</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393871</op_id>
  <op_id>1013393874</op_id>
  <op_id>1013393874</op_id>
  <op_id>1013393874</op_id>
  <op_id>1013393874</op_id>
  <op_id>1013393874</op_id>
  <op_id>1013393874</op_id>
  <op_id>4388803569</op_id>
  <op_id>4388803569</op_id>
  <op_id>4388803569</op_id>
  <op_id>4388803569</op_id>
  <op_id>4388803569</op_id>
  <op_id>1013679270</op_id>
  <op_id>1013679270</op_id>
  <op_id>1013679270</op_id>
  <op_id>1013679270</op_id>
  <op_id>1013679270</op_id>
  <op_id>1013679270</op_id>
  <op_id>1014142699</op_id>
  <op_id>1014142699</op_id>
  <op_id>1014142699</op_id>
  <op_id>1014142699</op_id>
  <op_id>1014142699</op_id>
  <op_id>1014142699</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870484</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870489</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870491</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870492</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870493</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870495</op_id>
  <op_id>1021870496</op_id>
  <op_id>1021870496</op_id>
  <op_id>1021870496</op_id>
  <op_id>1021870496</op_id>
  <op_id>1021870496</op_id>
  <op_id>1021870496</op_id>
  <op_id>4408034849</op_id>
  <op_id>4408034849</op_id>
  <op_id>4408034849</op_id>
  <op_id>4408034849</op_id>
  <op_id>4408034849</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870498</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870499</op_id>
  <op_id>1021870500</op_id>
  <op_id>1021870500</op_id>
  <op_id>1021870500</op_id>
  <op_id>1021870500</op_id>
  <op_id>1021870500</op_id>
  <op_id>1021870500</op_id>
  <op_id>1032140806</op_id>
  <op_id>1032140806</op_id>
  <op_id>1032140806</op_id>
  <op_id>1032140806</op_id>
  <op_id>1032140806</op_id>
  <op_id>1032140806</op_id>
  <op_id>1035310069</op_id>
  <op_id>1035310069</op_id>
  <op_id>1035310069</op_id>
  <op_id>1035310069</op_id>
  <op_id>1035310069</op_id>
  <op_id>1035310069</op_id>
</outProduct_10310>

I am totally new to xquery . Can anyone help me how to get unmatched data only?

score 2 · Accepted Answer · answered Aug 21 '12 at 14:23

First, You don't need to compare all pairs of ids with a for loop, because the standard = operator will search for an equal value, if you provide a sequence.

So an easier way to find all matching products is:

<outProduct_10310>
{ for $b in doc("sample1.xml")/data/row[product_id = doc("sample2.xml")/productSet/row/product]
  return   
      <op_id> { $b/product_id/text() } </op_id>
  }  
</outProduct_10310>

And for the not matching products you can just select all products, that don't match with the not operator: (notice that you can't use != here, because there will always be a different id)

<outProduct_10310>
{ for $b in doc("sample1.xml")/data/row[not (product_id = doc("sample2.xml")/productSet/row/product) ]
  return   
      <op_id> { $b/product_id/text() } </op_id>
  }  
</outProduct_10310>

Depending on how good your XQuery engine is, you might want to move the doc("sample2.xml")/productSet/row out of the [..] brackets and store it in a variable.

yea it worked. you showed the way. I have to learn more on xquery. Thanks for response — Satheesh Narayanan, Aug 21 '12 at 14:30

score 1 · Answer 2 · answered Aug 21 '12 at 14:17

1

Note that all general comparisons (=, !=, ...) will compare all items of the left operand with all items of the right operand. As soon as a comparison is successful (i.e., yields true), the result will be true as well. As an example, (1,2) != (1,2) will return true, because the single comparison 1 != 2 yields true.

In your particular query, you could try not(product = $b/product_id) instead, which will only return true if there is no single comparison that yields true.

answered Aug 21 '12 at 14:17

Christian Grün

6,012
18
34

Thanks for your reply. I tried not(product = $b/product_id) in my query but it returns same output. I cant get exact unmatched data – Satheesh Narayanan Aug 21 '12 at 14:24

How to get data from two XML file using Xquery?

2 Answers2