SSDN - Converting SQL query to XQuery

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Amy Gordon

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.

Any help would be appreciated.
Thanks!

(Deleted User)

Subject: Converting SQL query to XQuery
Author: (Deleted User)
Date: 22 Aug 2005 04:06 AM

Hi Amy,

>The query is as follows:
>
>SELECT ACTIVITIES_ITS_VESSEL.Activity_Number,
>VESSELSDATASHEET.Equipment_No,
>VESSELSDATASHEET.Function_Loc,
>SCHEDULE_ITS_VESSEL.DateScheduled,
>ACTIVITIES_ITS_VESSEL.Inspection_Task

Given these columns, you need this "return" statement:

return
<record>
{$act/Activity_Number}
{$vds/Equipment_No}
{$vds/Function_Loc}
{$sch/DateScheduled}
{$act/Inspection_Task}
</record>

Hope this helps,
Alberto

Amy Gordon

Subject: Converting SQL query to XQuery
Author: Amy Gordon
Date: 22 Aug 2005 09:29 PM

Thanks!
I assume that includes the inner joins that were present in the SQL statement?

(Deleted User)

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)

Hope this helps,
Alberto

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.