XQuery distinct values with where clause problem

Question

I'm very new to XQuery so excuse me if I am somehow missing something.

I am trying to extract data where certain sub-nodes of an element are DISTINCT, as well as where a certain sibling node's is equal to some predefined string

for $product in fn:distinct-values(document('cpwdoc')/root/package/properties/value[@key="product"])
where document('cpwdoc')/root/package/categories/category[@name="Cheap"]
return $product

The XML I am querying looks like this:

<root>
 <package>
      <title>Some package 1</title>
      <categories><category group="Other" name="Cheap"/></categories>
      <properties>
        <value key="product">BLUE-TOOTHBRUSH</value>
      </properties>
    </package>
 <package>
      <title>Some package 2</title>
      <categories><category group="Other" name="Expensive"/></categories>
      <properties>
        <value key="product">BLUE-TOOTHBRUSH</value>
      </properties>
    </package>
 <package>
      <title>Some package 3</title>
      <categories><category group="Other" name="Expensive"/></categories>
      <properties>
        <value key="product">TOOTHPASTE</value>
      </properties>
    </package>
</root>

So basically I want only DISTINCT occurances of the product, and only where the name attribute of the category is equal to "Cheap".

My query returns DISTINCT products, but the where clause seems to have no effect, it still returns products whose category is "Expensive".

Can anyone advise as to what I am doing wrong.

Oliver Hallam Oliver Hallam · Accepted Answer · 2010-09-13T17:46:20

Your where clause:

where document('cpwdoc')/root/package/categories/category[@name="Cheap"]

is expanded out to:

where boolean(document('cpwdoc')...)

which is equivalent to

where exists(document('cpwdoc')...)

and so you are returning all products as long as there is at least one cheap product.

You want something like the following

distinct-values(
  for $package in document('cpwdoc')/root/package
  let $value := $package/properties/value
  where $value/@key = "product" and $package/categories/category/@name="Cheap"
  return $value
)

which if you like path expressions is the same as

distinct-values(document('cpwdoc')/root/package[categories/category/@name="Cheap"]/properties/value[@key="product"])

XQuery distinct values with where clause problem

1 Answers