Click here to Skip to main content
15,892,674 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
In below xml:
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:
XML
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:
XML
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 ?
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900