exist db - XQuery v3 - comparing two values of two different files -

i assume, can solved using grouping, beginner in xquery, not quite able write functioning query. using exist-db, xquery version 3.

i have 2 xml documents. first 1 containing records :

<itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="1" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="complt16"/> <itemdata itemoid="suppdm.qlabel" value="completers of week 16 population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="2" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="complt24"/> <itemdata itemoid="suppdm.qlabel" value="completers of week 24 population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="3" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="complt8"/> <itemdata itemoid="suppdm.qlabel" value="completers of week 8 population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="4" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="efficacy"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="5" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="itt"/> <itemdata itemoid="suppdm.qlabel" value="intent treat population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="6" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="safety"/> <itemdata itemoid="suppdm.qlabel" value="safety population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="7" > <itemdata itemoid="suppdm.usubjid" value="01-701-1023"/> <itemdata itemoid="suppdm.qnam" value="efficacy"/> <itemdata itemoid="suppdm.qlabel" value="efficacy population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="8" > <itemdata itemoid="suppdm.usubjid" value="01-701-1023"/> <itemdata itemoid="suppdm.qnam" value="itt"/> <itemdata itemoid="suppdm.qlabel" value="intent treat population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="9" > <itemdata itemoid="suppdm.usubjid" value="01-701-1023"/> <itemdata itemoid="suppdm.qnam" value="safety"/> <itemdata itemoid="suppdm.qlabel" value="safety population flag"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="1198" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="race1"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="1199" > <itemdata itemoid="suppdm.usubjid" value="01-701-1015"/> <itemdata itemoid="suppdm.qnam" value="race2"/> </itemgroupdata> <itemgroupdata itemgroupoid="suppdm" data:itemgroupdataseq="1200" > <itemdata itemoid="suppdm.usubjid" value="01-701-1023"/> <itemdata itemoid="suppdm.qnam" value="race1"/> </itemgroupdata> 

the focus on last 3 records (‘racex’).

the second document has following records (with unique records, each has different usubjid).

<itemgroupdata itemgroupoid="dm" data:itemgroupdataseq="1" > <itemdata itemoid="dm.usubjid" value="01-701-1015"/> <itemdata itemoid="dm.race" value="multiple"/> </itemgroupdata> <itemgroupdata itemgroupoid="dm" data:itemgroupdataseq="2" > <itemdata itemoid="dm.usubjid" value="01-701-1023"/> <itemdata itemoid="dm.race" value="multiple"/> </itemgroupdata> 

as can see, suppdm has several records same usubjid can have several qnam values. want check, whether qnam usubjid “racex”, when dm.race multiple.

i tried write xquery , kind of works return somehow not return value of race.. can please me?

here xquery (in beginning, declared variables metadata xml file) :

xquery version "3.0"; declare namespace def = "http://www.cdisc.org/ns/def/v2.0"; declare namespace odm="http://www.cdisc.org/ns/odm/v1.3"; declare namespace data="http://www.cdisc.org/ns/dataset-xml/v1.0"; declare namespace xlink="http://www.w3.org/1999/xlink";  let $base := '/db/mydataset/' let $define := 'define_2_0.xml'  (: suppdm dataset :) let $suppdmdataset := doc(concat($base,$define))//odm:itemgroupdef[@name='suppdm'] let $suppdmdatasetname := $suppdmdataset/def:leaf/@xlink:href let $suppdmdatasetlocation := concat($base,$suppdmdatasetname)  (: dm dataset :) let $dmdataset := doc(concat($base,$define))//odm:itemgroupdef[@name='dm'] let $dmdatasetname := $dmdataset/def:leaf/@xlink:href let $dmdatasetlocation := concat($base,$dmdatasetname)  (:get usubjid in dm :) let $dmusubjidoid := (     $a in doc(concat($base,$define))//odm:itemdef[@name='usubjid']/@oid      $a = doc(concat($base,$define))//odm:itemgroupdef[@name='dm']/odm:itemref/@itemoid     return $a ) (:get usubjid in suppdm :) let $suppdmusubjidoid := (     $a in doc(concat($base,$define))//odm:itemdef[@name='usubjid']/@oid      $a = doc(concat($base,$define))//odm:itemgroupdef[@name='suppdm']/odm:itemref/@itemoid     return $a ) (: oid of race in dm dataset :) let $raceoid := (     $a in doc(concat($base,$define))//odm:itemdef[@name='race']/@oid      $a = doc(concat($base,$define))//odm:itemgroupdef[@name='dm']/odm:itemref/@itemoid     return $a ) (: oid of qnam in suppdm :) let $qnamoid := (     $a in doc(concat($base,$define))//odm:itemdef[@name='qnam']/@oid      $a = doc(concat($base,$define))//odm:itemgroupdef[@name='suppdm']/odm:itemref/@itemoid     return $a ) (:substring(qnam,1,4) = „race“ :)  (:we searching through subjects in dm have value "multiple" in race :)  $record in doc($dmdatasetlocation)//odm:itemgroupdata[odm:itemdata[@itemoid=$raceoid , upper-case(@value)='multiple']] (:or $record in doc($suppdmdatasetlocation)//odm:itemgroupdata[odm:itemdata[@itemoid=$qnamoid]]/starts-with(@value,"race") :)      let $recnum := $record/@data:itemgroupdataseq  (: dm usubjid value :) let $dmusubjidvalue := $record/odm:itemdata[@itemoid=$dmusubjidoid]/@value  (: value of qnam in suppdm subject corresponding usubjid - should "race1", race2 or ... "racen"; principally should start 'race' :) let $qnamvalue := (     $a in doc($suppdmdatasetlocation)//odm:itemgroupdata[odm:itemdata[@itemoid=$suppdmusubjidoid , @value=$dmusubjidvalue]]     let $b := doc($suppdmdatasetlocation)//odm:itemgroupdata[odm:itemdata[@itemoid=$qnamoid]]/upper-case(@value)     (starts-with($b, 'race'))     return $a )  (:now check, whether value qnam starts 'race' :)  (:  not(starts-with($qnamvalue, "race")):)      return <warning recordnumber="{data($recnum)}">invalid value qnam in dataset {data($suppdmdatasetname)} - label={data($qnamvalue)}. => race in {data($dmdatasetlocation)} subject {data($dmusubjidvalue)} 'multiple', qnam should in (race1, race2,... racen). if race 'multiple', additional information has added supplemental qualifier dataset of dm  (suppdm)       </warning> 

i not sure where expression. unfortunately, output looks this: "invalid value qnam in dataset suppdm.xml label=. =>" xquery not return $qnamvalue… empty.

(update 12/05/2015) here post new xquery based on suggestion of adamretter!

for $record in doc($dmdatasetlocation)//odm:itemgroupdata let $recnum := $record/@data:itemgroupdataseq  (: value of usubjid :) let $dmusubjidvalue := $record//odm:itemdata[@itemoid=$dmusubjidoid]/@value  (:check, whether usubjid has record in suppdm has qnam in 'racex' :) let $multiple-usubjid :=     not(empty(doc("$dmdatasetlocation")//odm:itemgroupdata[odm:itemdata[@itemoid =$dmusubjidoid][@value =$dmusubjidvalue]][odm:itemdata[@itemoid eq $raceoid][@value eq "multiple"]]     )) return (:it return give return if there exists usubjid has qnam matches race :)     if($multiple-usubjid)         doc($suppdmdatasetlocation)//odm:itemgroupdata[odm:itemdata[@itemoid = $suppdmusubjidoid][@value = $dmusubjidvalue]][odm:itemdata[@itemoid =$qnamoid]                [fn:matches(@value, "race[0-9]+")]]         else(   <warning rule="spc_dm_race_01" rulelastupdate="2015-02-10" recordnumber="{data($recnum)}">invalid value qnam in dataset {data($suppdmdatasetname)} - label={data($multiple-usubjid)}. => race in {data  ($dmdatasetlocation)} subject {data($dmusubjidvalue)} 'multiple', qnam should in (race1, race2,... racen). if race 'multiple', additional information has added supplemental qualifier dataset of dm  (suppdm)         </warning> 

i quite understood expressions, how can retrieve right qnam value race in it? query doesn't work way want , somehow records returned dm.

at least, value multiple-usubjid (false or true) want recieve qnam value specific usubjid has race in it.
real beginner , don't quite know have put else expression, or how have "reorganize" query.

again, thank help! christiane

you looking simple join across 2 files, although can without join. have 2 queries, , second query needs executed if first query matches.

to simplify issue stored 2 xml files /tmp/doc1.xml , /tmp/doc2.xml, , focused on finding nodes interested in, lot of xquery above not focused on core problem experiencing. anyway, here proposed solution -

xquery version "1.0";  (: usubjid interested in :) declare variable $local:usubjid := "01-701-1015";  let $multiple-usubjid :=     not(empty(         doc("/tmp/doc2.xml")             //itemgroupdata[itemdata[@itemoid eq "dm.usubjid"]                [@value eq $local:usubjid]][itemdata[@itemoid eq "dm.race"]                [@value eq "multiple"]]     )) return     if($multiple-usubjid)         doc("/tmp/doc1.xml")             //itemgroupdata[itemdata[@itemoid eq "suppdm.usubjid"]                [@value eq $local:usubjid]][itemdata[@itemoid eq "suppdm.qnam"]                [fn:matches(@value, "race[0-9]+")]]     else() 

now extracted function or rewritten more efficient if want test usubjid, starting point discussion.