How to write a xQuery to extract two fields based on the conditions of others value?

Question

I have an XML document:

<resultsets>
    <row>
        <emp_no>10001</emp_no>
        <first_name>Georgi</first_name>
        <last_name>Facello</last_name>
    </row>
    <row>
        <emp_no>10002</emp_no>
        <first_name>Bezalel</first_name>
        <last_name>Simmel</last_name>
    </row>
</resultset>

I want to write a xQuery using FLWOR to extract emp_no and first_name where emp_no is 10001 with the coressponding first name like this:

<row>
     <emp_no>10001</emp_no>
     <first_name>Georgi</first_name>
</row>

The code I wrote:

for $id in doc("employees.xml")//emp_no
for $first in doc("employees.xml")//first_name
where ($id/text() = '10001')
return 
    <row>
    <id>{upper-case($id/text())}</id>
    <first>{$first/text()}</first>
    </row>

however, it returns a cartesian product of $id and $first,

<row>
    <emp_no>10001</emp_no>
    <first_name>Georgi</first_name>
</row>
<row>
    <emp_no>10001</emp_no>
    <first_name>Bezalel</first_name>
</row>

Do you know how to fix this using xQuery FLWOR? Thanks!

score 0 · Accepted Answer · answered Feb 15 '18 at 21:30

I would simply select the row and then use a where clause on the emp_no child:

for $row in /resultsets/row
where $row/emp_no = '10001'
return
    <row>
        <id>{$row/emp_no/data()}</id>
        <first>{$row/first_name/data()}</first>
    </row>

http://xqueryfiddle.liberty-development.net/bFukv89

It is not quite clear whether you want to change the element names from emp_no to id and from first_name to first, if that is not needed you can of course simply copy them:

for $row in /resultsets/row
where $row/emp_no = '10001'
return
    <row>
        {$row/emp_no, $row/first_name}
    </row>

http://xqueryfiddle.liberty-development.net/bFukv89/1

How to write a xQuery to extract two fields based on the conditions of others value?

1 Answers1