In below xml:
<mo>
<customers>
<customer cno="2222">
<cname>Charles</cname>
<street>123 Main St.</street>
<city>Wichita</city>
<zip>67226</zip>
<phone>316-636-5555</phone>
</customer>
<customer cno="1000">
<cname>Bismita</cname>
<street>Ashford Dunwoody</street>
<city>Wichita</city>
<zip>67226-1555</zip>
<phone>000-000-0000</phone>
</customer>
</customers>
<employees>
<employee eno="1000">
<ename>Jones</ename>
<city>Wichita</city>
<zip>67226-1555</zip>
<hdate>1995-12-12</hdate>
</employee>
<employee eno="2000">
<ename>Asmit</ename>
<city>Wichita</city>
<zip>67226-1555</zip>
<hdate>1967-08-13</hdate>
</employee>
<employee eno="1003">
<ename>Axaya</ename>
<city>BBSR</city>
<zip>67226</zip>
<hdate>1978-08-13</hdate>
</employee>
</employees>
<parts>
<part pno="10506">
<pname>Land Before Time I</pname>
<qoh>200</qoh>
<price>319.99</price>
<level>20</level>
</part>
<part pno="10000">
<pname>Bottle</pname>
<qoh>2</qoh>
<price>3.00</price>
<level>2</level>
</part>
<part pno="10508">
<pname>Land Before Time 3</pname>
<qoh>202</qoh>
<price>2.00</price>
<level>22</level>
</part>
<part pno="10509">
<pname>Cycle</pname>
<qoh>202</qoh>
<price>1.00</price>
<level>22</level>
</part>
</parts>
<orders>
<order ono="1000" takenBy="1000" customer="2222" >
<receivedDate>1967-08-17</receivedDate>
<shippedDate>1967-08-13</shippedDate>
<items>
<item>
<partNumber>10508</partNumber>
<quantity>2</quantity>
</item>
</items>
</order>
<order ono="1001" takenBy="1000" customer="1000" >
<receivedDate>1968-08-14</receivedDate>
<shippedDate>1968-08-11</shippedDate>
<items>
<item>
<partNumber>10000</partNumber>
<quantity>2</quantity>
</item>
</items>
</order>
<order ono="1022" takenBy="1003" customer="2222" >
<receivedDate>1995-02-14</receivedDate>
<shippedDate>1995-02-13</shippedDate>
<items>
<item>
<partNumber>10000</partNumber>
<quantity>1</quantity>
</item>
<item>
<partNumber>10508</partNumber>
<quantity>2</quantity>
</item>
<item>
<partNumber>10509</partNumber>
<quantity>3</quantity>
</item>
</items>
</order>
</orders>
</mo>
Two xquries are required.
1. Get the names of customers who have ordered parts from employees living in Wichita.
I have tried below xquery:
for
$c in /mo/customers/customer,
$e in /mo/employees/employee,
$p in /mo/parts/part,
$o in /mo/orders/order
where data($p/@pno)=$o/items/item/partNumber
and data($e/@eno)= data($o/@takenBy)
and $e/city='Wichita'
return $c/cname
Problem: Query returns duplicate values, how can I get distinct values ?
2. Get the names of customers who have ordered parts only from employees living in Wichita.
I have tried below xquery:
for
$c in /mo/customers/customer,
$e in /mo/employees/employee,
$p in /mo/parts/part,
$o in /mo/orders/order
where data($p/@pno)=$o/items/item/partNumber
and data($e/@eno)= data($o/@takenBy)
and data($e/city) eq("Wichita")
return $c/cname
Problem: Same result as query 1 with duplicate values.
Note: what should be the difference between two query by observing "living in Wichita and living only in wichita".
How can I get rid of returning duplicate values in these queries ?
Any help ?