Let me first say hello, not been here before, looks likea nice site.

Down to Business :twisted:

I need some help, i have two SQL statements which i need to change into XQuery. I have had some limited sucess with them, but no really luck with getting them done.

Oh and im running the XQuery statements using the Northwinds Database Exported in XML format.

The First SQL Statement is :

SELECT count(*)
FROM shippers
LEFT join orders ON orders.shipvia = shippers.shipperid
WHERE shippers.companyname = ‘United Package’

I have managed to get the Join to work in XQuery using:

xquery version “1.0”;

{

for $x in doc(“Orders.xml”)/dataroot/Orders
for $y in doc(“Shippers.xml”)/dataroot/Shippers

where $x/ShipVia = $y/ShipperID and $y/CompanyName = “United Package”

return

}

However not being able to impliment the Count()

Any ideas or fixed modified code would be greatly recieved.

The second SQL statement needing changing is:

SELECT firstname, lastname, hiredate
FROM employees
WHERE hiredate > ‘12/31/92’ AND hiredate < ‘01/01/94’

Now with this i have produced :

xquery version “1.0”;

{

for $x in doc(“Employees.xml”)/dataroot/Employees

        where $x/HireDate>= "1992-07-06" and $x/HireDate <= "1993-15-12" 

return <Hired> {($x/FirstName)} {($x/LastName)} {($x/HireDate)}</Hired>

}

With this i get an error about DateTime ?

ANY help with either of these would be amazing

Thanks for Reading and taking the time to help me.

Kind Regards

Dave

You probably should have posted this in the XQuery forum, since it doesn’t involve the Java API at all, but I’ll take a stab at this. One caveat: I don’t really know SQL all that well.

Anyway, here’s how I’d rewrite your first XQuery expression: if I understand the SQL correctly, what you really want is how many orders are being shipped via ‘United Package’, but the order documents use a shipper id rather than the name. Try this:

let $shipid := doc("Shippers.xml")/dataroot/Shippers[CompanyName = "United Package"]/ShipperID
return count(doc("Orders.xml")/dataroot/Orders[ShipVia = $shipid])

Note that XQuery does have a count() function. I realize this doesn’t look a lot like the SQL, but then XQuery isn’t SQL, is it?

If you really want to return the order numbers and not just the count, try it like this:

<pretty>{
let $shipid := doc("Shippers.xml")/dataroot/Shippers[CompanyName = "United Package"]/ShipperID
for $order in doc("Orders.xml")/dataroot/Orders
where $order/ShipVia = $shipid
return <order>{$order/OrderID}</order>
}</pretty>

Or, to make it even closer to your original expression:

<pretty>{
for $order in doc("Orders.xml")/dataroot/Orders,
$shipper in doc("Shippers.xml")/dataroot/Shippers
where $order/ShipVia = $shipper/ShipperID and $shipper/CompanyName = "United Package"
return <order>{$order/OrderID, $shipper/CompanyName}</order>
}</pretty>

The problem with your second one is that XQuery is strongly typed, and your HireDate node is defined as xs:date and you’re trying to compare it to a string. You need to explicitly cast it to the proper type:

<northwind>{
for $emp in doc("Employees.xml")/dataroot/Employees
where $emp/HireDate >= xs:date("1992-07-06") and $emp/HireDate <= xs:date("1993-15-12")
return <Hired>{$emp/FirstName, $emp/Lastname, $emp/HireDate}</Hired>
}</northwind>