xquery help to query products and users from a different element node -
please me understand how write kind of query xquery. have .xml:
<auctions xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> <products> <product id="1"> <name>name1</name> </product> <product id="2"> <name>name2</name> </product> <product id="3"> <name>name3</name> </product> <product id="4"> <name>name4</name> </product> </products> <users> <user username="kukuk1"> </user> <user username="kukuk2"> </user> <user username="kukuk3"> </user> </users> <bids> <product id="1"> <bid user="kukuk1">400</bid> <bid user="kukuk2">410</bid> <bid user="kukuk1">450</bid> </product> <product id="2"> <bid user="kukuk3">200</bid> <bid user="kukuk2">300</bid> </product> <product id="3"> <bid user="kukuk1">150</bid> </product> </bids> </auctions> and need output, follows: user "kukuk1" got products "name1" (with value "450") , "name3" (with value "150). user "kukuk3" has not won products. user "kukuk2" won products "name2".
the elements should ordered user ascending , elements product value descending, should this:
<got> <user name="kukuk1"> <product value="450">name1</product> <product value="150">name3</product> </user> <user name="kukuk3"/> <user name="kukuk2"> <product value="300">name2</product> </user> </got> this got far:
declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization"; declare option output:item-separator "
"; <got>
{ $u in (//auctions/users/user) let $p:= //auctions/products let $v := //auctions/bids/product let $max1 := max($v/bid) let $max2 := max($v/bid[4]) let $got := //auctions/bids/product let $won-product := $got[@id=$p/product/@id] order $u return if ($u/@username="kukuk1") (<user name="{fn:string($u/@username)}"> 
<product value="{$max1}">{fn:string($p/product[1]/name)}</product> 
</user>,'
') else if ($u/@username="kukuk3") (<user name="{fn:string($u/@username)}"> 
<product value="{$max2}">{fn:string($p/product[2]/name)}</product>
</user>,'
') else if ($u/@username="kukuk2") (<user name="{fn:string($u/@username)}"> 
<product value="{$max2}">{fn:string($p/product[3]/name)}</product>
 </user>,'
') else () } </got> and i'm getting output:
<got> <user name="kukuk1"> <product value="450">name1</product> </user> <user name="kukuk3"> <product value="">name2</product> </user> <user name="kukuk2"> <product value="">name3</product> </user> </got>
you need 2 loops achieve result describe. in outer loop order users based on respective username , in inner loop bids , order them highest value.
hence, should this:
<got>{ $u in //auctions/users/user let $username := $u/@username order $username return element user { $username, $product in //auctions/bids/product[bid/@user = $username] let $highest-bid := max($product/bid) order $highest-bid descending return if ($product/bid[. = $highest-bid , @user = $username]) element product { attribute {"value"} {$highest-bid}, //auctions/products/product[@id = $product/@id]/name/string() } else () } }</got> please note example output not fit description kukuk3 > kukuk2 , therefore should order way. assumed description correct.
Comments
Post a Comment