XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Amy GordonSubject: 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!

Postnext
Alberto MassariSubject: Converting SQL query to XQuery
Author: Alberto Massari
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

Postnext
Amy GordonSubject: 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?

Posttop
Alberto MassariSubject: Re: Converting SQL query to XQuery
Author: Alberto Massari
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


 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! 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

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
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.