CODEWITHSUNDEEP
sql-serverxmlxqueryflwor
dgcharitha
dgcharitha

Reputation: 345

Empty results when filtering based on position in XQuery

I tried to print the details of 3rd and 4th products in the catalog, but I am getting empty results.

XML input:

<catalog>
  <product dept="WMN">
    <number>557</number>
    <name language="en">Fleece Pullover</name>
    <colorChoices>navy black</colorChoices>
  </product>
  <product dept="ACC">
    <number>563</number>
    <name language="en">Floppy Sun Hat</name>
  </product>
  <product dept="ACC">
    <number>443</number>
    <name language="en">Deluxe Travel Bag</name>
  </product>
  <product dept="MEN">
    <number>784</number>
    <name language="en">Cotton Dress Shirt</name>
    <colorChoices>white gray</colorChoices>
    <desc>Our <i>favorite</i> shirt!</desc>
  </product>
</catalog>

what I tried:

SELECT xDoc.query('  for $prod in //product
let $x:=$prod
return (<Item>{data($x[3])}{data($x[4])}</Item>)') 
FROM  AdminDocs
where  id=6

Output displayed:

<Item />
<Item />
<Item />
<Item />

Upvotes: 1

Views: 112

Answers (1)

Jens Erat
Jens Erat

Reputation: 38732

This defines a loop over all products:

for $prod in //product

For each of those products, you return the third and fourth node out of a sequence of a single product, which obviously will yield empty nodes:

let $x:=$prod
return (<Item>{data($x[3])}{data($x[4])}</Item>)

For a better understanding, return the whole product node each time:

for $prod in //product
return <item>{ $prod }</item>

Which will result in something like this:

<item>
  <product dept="WMN">
    <number>557</number>
    <name language="en">Fleece Pullover</name>
    <colorChoices>navy black</colorChoices>
  </product>
</item>
<item>
  <product dept="ACC">
    <number>563</number>
    <name language="en">Floppy Sun Hat</name>
  </product>
</item>
<item>
  <product dept="ACC">
    <number>443</number>
    <name language="en">Deluxe Travel Bag</name>
  </product>
</item>
<item>
  <product dept="MEN">
    <number>784</number>
    <name language="en">Cotton Dress Shirt</name>
    <colorChoices>white gray</colorChoices>
    <desc>Our <i>favorite</i> shirt!</desc>
  </product>
</item>

And each of those <product/> elements being a $prod[1]. Try:

for $prod in //product
return <item>{ $prod[1] }</item>

which will return the exactly same result, compared to

for $prod in //product
return <item>{ $prod[2] }</item>

(or any other positional predicate) resulting in empty <item/> nodes.

Finally, to return only the third and fourth products, shift the positional predicate into the for loop argument:

for $prod in //product[position() = 3 or position() = 4]
return <item>{ $prod }</item>

(and apply data(...) as needed, but I'd guess it won't yield the results you expect here).

Upvotes: 3

Related Questions