Subject:Converting SQL query to XQuery Author:Amy Gordon Date:15 Aug 2005 11:11 PM
Hi everyone!
I'm trying to convert an SQL query into XQuery format (we're viewing the relational database in an XML view). I've had a look at the XQuery tutorials but how to replicate an inner join is confusing me.
The query is as follows:
SELECT ACTIVITIES_ITS_VESSEL.Activity_Num ber, VESSELSDATASHEET.Equipment_No, VESSELSDATASHEET.Function_Loc,
SCHEDULE_ITS_VESSEL.DateScheduled, ACTIVITIES_ITS_VESSEL.Inspection_T ask
FROM ACTIVITIES_ITS_VESSEL INNER JOIN
VESSELSDATASHEET ON ACTIVITIES_ITS_VESSEL.Vessel_Equip ment_ID = VESSELSDATASHEET.Vessel_Equipment_ ID INNER JOIN
SCHEDULE_ITS_VESSEL ON ACTIVITIES_ITS_VESSEL.Vessel_Equip ment_ID = SCHEDULE_ITS_VESSEL.Vessel_Equipme nt_ID AND
ACTIVITIES_ITS_VESSEL.Activity_Num ber = SCHEDULE_ITS_VESSEL.Activity_Numbe r
And the XQuery code I have so far is:
<query>
{
for $act doc("database.xml") /DATABASE/ACTIVITIES_ITS_VESSEL
$vds doc("database.xml) DATABASE//VESSELSDATASHEET
$sch doc("database.xml) /DATABASE/SCHEDULE_ITS_VESSEL
where $act/Vessel_Equipment_ID = $vds/Vessel_Equipment_ID
and $act/Vessel_Equipment_ID = $sch/Vessel_Equipment_ID
and $act/Activity_Number = $sch/Activity_Number
return
<record>
</record>
}
</query>
However I'm not sure what to put in the return section to emulate the inner join.
Subject:Re: Converting SQL query to XQuery Author:(Deleted User) Date:25 Aug 2005 05:42 AM
Hi Amy,
>I assume that includes the inner joins that were present in the SQL statement?
Yes, the behavior of a FLWOR loop on multiple variables with a
"where" condition testing all of them is the same of a INNER JOIN
statement (i.e. the "return" expression will be evaluated only if all
the IDs match)