Subject: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.