I have below xml,which contains a list of customers and their orders and items in orders,I want to get a list contains distinct values of customers, customer's id is the attribute of "Name":
<customersunion>
<Customer Name="c2">
<Order OrderNumber="o1">
<Item Description="xyz"> i10 </Item>
</Order>
</Customer>
<Customer Name="c3">
<Order OrderNumber="o1">
<Item Description="qwe"> i11 </Item>
<Item Description="mnb"> i12 </Item>
</Order>
<Order OrderNumber="o2">
<Item Description="cfg"> i5 </Item>
</Order>
</Customer>
<Customer Name="c1">
<Order OrderNumber="o1">
<Item Description="abc"> i1 </Item>
<Item Description="def"> i2 </Item>
</Order>
</Customer>
<Customer Name="c4">
<Order OrderNumber="o1">
<Item Description="abc"> i1 </Item>
</Order>
<Order OrderNumber="o2">
<Item Description="def"> i2 </Item>
</Order>
</Customer>
<Customer Name=" c2 ">
<Order OrderNumber=" o1 ">
<Item Description=" milk "/>
</Order>
</Customer>
<Customer Name=" c10 ">
<Order OrderNumber=" o1 ">
<Item Description=" chips greek yogort avacado "/>
</Order>
</Customer>
<Customer Name=" c4 ">
<Order OrderNumber=" o1 ">
<Item Description=" tea bags cheese "/>
</Order>
</Customer>
<Customer Name=" c11 ">
<Order OrderNumber=" o1 ">
<Item Description=" milk sushi Notebook "/>
</Order>
</Customer>
<Customer Name=" c11 ">
<Order OrderNumber=" o2 ">
<Item Description=" grape tomato "/>
</Order>
</Customer>
<Customer Name=" c12 ">
<Order OrderNumber=" o1 ">
<Item Description=" bread "/>
</Order>
</Customer>
</customersunion>
and below is the result I want to get
<customers>
<Customer Name="c1">
<Order OrderNumber="o1">
<Item Description="abc">
i1
</Item>
<Item Description="def">
i2
</Item>
</Order>
</Customer>
<Customer Name="c2">
<Order OrderNumber="o1">
<Item Description="xyz">
i10
</Item>
</Order>
</Customer>
<Customer Name="c3">
<Order OrderNumber="o1">
<Item Description="qwe">
i11
</Item>
<Item Description="mnb">
i12
</Item>
</Order>
<Order OrderNumber="o2">
<Item Description="cfg">
i5
</Item>
</Order>
</Customer>
<Customer Name="c4">
<Order OrderNumber="o1">
<Item Description="abc">
i1
</Item>
</Order>
<Order OrderNumber="o2">
<Item Description="def">
i2
</Item>
</Order>
</Customer>
<Customer Name="c10">
<Order OrderNumber="o1">
<Item Description="chips">i1</Item>
<Item Description="greek yogort">i2</Item>
<Item Description="avacado">i3</Item>
</Order>
</Customer>
<Customer Name="c11">
<Order OrderNumber="o1">
<Item Description="milk">i1</Item>
<Item Description="sushi">i2</Item>
<Item Description="Notebook">i3</Item>
</Order>
<Order OrderNumber="o2">
<Item Description="grape tomato">i1</Item>
</Order>
</Customer>
<Customer Name="c12">
<Order OrderNumber="o1">
<Item Description="bread">i1</Item>
</Order>
</Customer>
</customers>
I used follow xQuery
let $customername:=doc("customerunion.xml")/Customer/@Name
for $uniquecustomer in distinct-values($customerunion/customersunion/Customer/@Name)
let $order:=distinct-values($customerunion/customersunion/Customer[Name=$uniquecustomer]/Order)
let $item:=distinct-values($customerunion/customersunion/Customer[Name=$uniquecustomer]/Order/Item)
where $uniquecustomer=$customername
return
<customerlib>
{$uniquecustomer/..}
{
for $o in $order return $o
}
{
for $i in $item return $i
}
</customerlib>
However, I got 0 line of result, could you help me to modify to xQuery then I can get the desired result?
No comments:
Post a Comment