Compare Master data with elements present in the XML

Question

<MasterData>
  <Name>AA</Name>
  <EmpId>123</EmpId>
  <AccountNo>111</AccountNo>
   <IFSC>ABC</IFSC>
  <AccountData>
    <AccountNo>111</AccountNo>
    <IFSC>ABC</IFSC>
  </AccountData>
  <AccountData>
    <AccountNo>222</AccountNo>
    <IFSC>DEF</IFSC>
  </AccountData>
</MasterData>

I have an xml like this in my database,I have a requirement to check the combination of AccountNo+IFSC present in the MasterData(not under the AccountData section) and compare with all documents present in the collection and check whether its matching to the data present in the AccountData section,If its matching identify the URI of the document. First identify the unique combination of AccountNo+IFSC from Masterdata section and then check whether this combination present under any of the AccountData section, there are more elements in this xml other than AccountNo and IFSC

Answer 1

If you had range indexes on the AccountNo and IFSC elements, then you could:

1. retrieve the set of values from AccountNo, IFSC, and a cts:uri-reference() with cts:value-tuples().
2. create a map using a composite key with the AccountNo and IFSC values and the URIs as the values for those map entries
3. prune any entry that only has one URI associated
4. return the map that will have the set of URIs corresponding to each combo of AccountNo and IFSC value

Something like this:

let $accountNumber-IFSC := 
  cts:value-tuples(
    (
      cts:element-reference(xs:QName("AccountNo")), 
      cts:element-reference(xs:QName("IFSC")),
      cts:uri-reference()
    )
   )
let $map := map:new()
let $_create_map_value_to_uris := for $co-occurrence in $accountNumber-IFSC
let $key := concat($co-occurrence[1], " ", $co-occurrence[2])
let $value := (map:get($map, $key), $co-occurrence[3])
return map:put($map, $key, $value)

let $_prune_single_uri :=  
  for $key in map:keys($map)
  let $value := map:get($map, $key)
  where not(tail($value))
  return 
    map:put($map, $key, ())
  
return 
  $map

If you just wanted the list of URIs, you can invert the map: -$map and return it's keys: return map:keys(-$map)

If you had a range-index on the EmpId you could pivot on that instead of the document URIs.