Using xquery FLWOR expressions to find multiple "where" restrictions

Question

I am trying to find employees who work on projects located in Houston but the department the project is housed in is not located in Houston. I was trying to model the expression after this example of FLWOR expressions but the query doesn't return anything and it should return results.

edit: Here is the input.

xquery
let $doc := doc("~path/company.xml")
for $e in $doc//employee,
    $d in $doc//department,
    $p in $doc//projects
where $d/locations[location!="Houston"]
and $p/project[plocations="Houston"]
return <e>{$e/fname}{$e/lname}{$e/address}</e>
/

Fantasia

You forgot to show us the XML document. – Astaire

Sorry I added the input file. – Fantasia

Answer 1

One for clause is enough; otherwise, you'll iterate over all employees several times:

let   $doc := doc(...)
for   $e in $doc//employee
let   $p := $doc//project[@pnumber = $e/projects/worksOn/@pno]
where $p[plocation = 'Houston']
  and $doc//department[@dno = $p/@controllingDepartment]
                      [not(locations/location = 'Houston')]
return <e>{ $e/fname }{ $e/lname }{ $e/address }</e>

Brandtr

Sorry I added the input file. – Fantasia

Slightly edited; this version of the query will be evaluated faster by some XQuery processors (such as BaseX). – Antifebrile

Answer 2

A single XPath 2.0 expression can select all wanted employees:

/*/employees
     /*[projects/*/@pno
       =
        /*/projects/*
               [plocation eq 'Houston'
              and
                boolean
                (for $dn in @controllingDepartment
                  return
                   /*/departments/*
                       [@dno eq $dn
                      and
                        not(locations/location
                          =
                           'Houston'
                           )
                       ]
                )
               ]/@pnumber
       ]

Astaire

Is XPath better in some way than using XQuery? – Fantasia

Actually, the only advantage using pure XPath is that you may also use it with processors that don’t support XQuery. – Antifebrile

@mnky9800n: @_Christian Grün answered your question. In a senses, being able to express something in pure XPAth makes your solution ultimately more portable. For example, you can evaluate this XPath expression not only with your XQuery processor (XPath is a subset of XQuery) but also in any XSLT 2.0 or 3.0 XSLT processor or in any stand-alone XPath 2.0 or 3.0 engine (Alas I am aware only of PsychiPath). – Astaire

@mnky9800n: Also some people prefer the tighter syntax of XPath over the "verbose" XQuery. XPath becomes even more attractive in 3.0 with the higher-order functions (HOF) and the ability to define inline, anonymous function items. My guess is that for 4.0 we'll seriously need an .xpath file extension and an "import clause" for XPath. (: Hope this comes soon :) – Astaire

Answer 3

Typo: and $p/project[plocation="Houston"]

You had an "s" too much: was plocations.

Intemperance

Hmm, this produces a lot of replication. I tried distinct-values(query above) but it says there is a syntax error however it is recommended to do it that way from this stackoverflow post. – Fantasia