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
Neal  WaltersSubject: Left Outer Join in XQuery
Author: Neal Walters
Date: 12 May 2005 06:38 PM
Here is my current Xquery that works:
<Root>
{
for $Message in doc("\\odimo.com\dfs01$\biztalk\Odimo\Amazon\FileDrop\Diamond\Settlement__96\Doc_Original_Backup\110791743-83bc248-85bfa00-8400050-861a8a0-85bfae8.xml")
/AmazonEnvelope/Message/SettlementReport/Order
return
<AmazonError>
<SettlementOrderID>{$Message/AmazonOrderID/text()}</SettlementOrderID>
<MerchantOrderID>{$Message/MerchantOrderID/text()}</MerchantOrderID>
{
for $result in doc("\\itdev15\Vish\settlement.xml")
/AmazonOrders/tbAmazonOrder[AmazonOrderID=$Message/AmazonOrderID]
return
<DBOrderID>
{$result/AmazonOrderID}
</DBOrderID>
}
</AmazonError>
}
</Root>

The downside is that it returns all the $Messages. How can I enhance to only select $Messages where there is no matching $result.

In other words, I'm trying to 1 out of 1000 $Messages that don't have a corresponding order on $result. The above example prints all 1000 messages.

Thanks in advance,
Neal Walters
http://Biztalk-Training.com

Posttop
Alberto MassariSubject: Left Outer Join in XQuery
Author: Alberto Massari
Date: 13 May 2005 03:23 AM
Hi Neal,
you can add a "where" condition like this

<Root>
{
for $Message in doc("110791743-83bc248-85bfa00-8400050-861a8a0-85bfae8.xml")
/AmazonEnvelope/Message/SettlementReport/Order
where empty(doc("settlement.xml")
/AmazonOrders/tbAmazonOrder[AmazonOrderID=$Message/AmazonOrderID])
return
<AmazonError>
<SettlementOrderID>{$Message/AmazonOrderID/text()}</SettlementOrderID>
<MerchantOrderID>{$Message/MerchantOrderID/text()}</MerchantOrderID>
<DBOrderID>
{$Message/AmazonOrderID}
</DBOrderID>
</AmazonError>
}
</Root>

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.