XQuery - Join query

3
votes

I have two XML files and want to join them to get the desired output.

XML File -1 

<Countries>
<Country>
   <No C="1"/>
   <Name>India</Name>
</Country>
<Country>
   <No C="2"/>
   <Name>USA</Name>
</Country>
<Country>
   <No C="3"/>
   <Name>Srilanka</Name>
</Country>
<Country>
   <No C="4"/>
   <Name>Thailand</Name>
</Country>
</Countries>

XML File - 2

<Capitals>
  <Capital>
      <No C="1"/>
      <Name>New Delhi</Name>
  </Capital>
  <Capital>
      <No C="2"/>
      <Name>Washington DC</Name>
  </Capital>
  <Capital>
      <No C="3"/>
      <Name>Colombo</Name>
  </Capital>
  <Capital>
      <No C="4"/>
      <Name>Bangkok</Name>
  </Capital>
</Capitals>

Output desired - 

India - New Delhi
USA - Washington DC
Srilanka - Colombo
Thailand - Bangkok

What I have tried is like, but unable to get the desired output :(

for $x in doc('Country')/Countries
  return 
  <a>
 {$x/Country/Name}
 {
   for $y in doc('Capital')/Capitals
   where $y/Capital/No/@C = $x/Country/No/@C
    return $y/Capital/Name
  }
  </a>
2
xmljoinxquery

2 Answers

3
votes

I got the answer and it is -

TWO-Way Join in a Predicate - 

for $x in doc('country')//Country
  return 
  string-join( ($x/Name/text(),  
     for $y in doc('capital')//Capital
       where $y/No/@C = $x/No/@C
       return $y/Name/text()
                ), " - ")
2
votes

Your answer yields the correct results, however I find the following syntax (using XPath instead of another FLOWR expressions) much simpler:

for $x in doc('country')//Country
return 
  string-join( ($x/Name/text(),  
    $doc('capital')//Capital[./No/@C = $x/No/@C]/Name/text()
            ), " - ")