Compare Master data with elements present in the XML

user_17596734 0
Viewed 34
<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

marklogic xquery
2 Answers

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.

Mads_Hansen_144190

Using the Optic API functions, you can do something similar with element-range indexes:

import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

op:from-lexicons(
  map:entry("AccountNo", cts:element-reference(xs:QName("AccountNo"))) 
  => map:with("IFSC", cts:element-reference(xs:QName("IFSC")))
  => map:with("URI", cts:uri-reference())
)
=> op:group-by(
    ("IFSC", "AccountNo"), 
    (
      op:group-concat("URIs", "URI", map:entry("separator", ", ")), 
      op:count("count", op:col("URI"))
    )
  )
=> op:where(op:gt(op:col("count"), 1))
=> op:result()
Mads_Hansen_144190
Related