Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

with the following content:

ID = 1
OutputPayload:

<ReportDocument>
<ALMSimulationResult>
<selectedModelAssets>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01" description="Global Bond" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0" initialCostUpFront="0.02" regularCostUpFront="0.015" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF00FFD4" stochasticDuration="5">
<models>
<model ID="0e70216f-48ce-4f6c-b2d6-519a5cdfd246" type="corporate grade bond" description="Eurozone Corporate Bond Intermediate (D=5Years)" weight="1" />
</models>
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec" description="America Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0.005" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF3B00FF" stochasticDuration="13.55">
<models>
<model ID="b0817f64-5090-48a3-b58c-aa8f6e5bbdc1" type="equity" description="US Value Style (Eur)" weight="0.9" />
<model ID="677e8aae-7b32-4dc3-88c5-e9302dddad8f" type="conventional bond" description="Euro Cash (TBill)" weight="0.1" />
</models>
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" description="Europa Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0.001" annualPerformanceFee="0.001" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF9D00FF" stochasticDuration="17.5">
<models>
<model ID="86fedd24-2a92-422c-b733-17c60105ff81" type="equity" description="Asia Value Style (Eur)" weight="0.1" />
<model ID="55425529-8adc-47d8-a36d-8cfd9da34880" type="conventional bond" description="Italian Long Term Gov Bond" weight="0.1" />
<model ID="fea29db9-cf0e-4802-bcbe-e2b8d367f0ca" type="cash" description="Euro Cash (Euribor 1m)" weight="0.1" />
<model ID="8e58d785-5fc5-4ede-8ec8-eb1af8e62541" type="equity" description="Eurozone Value Style" weight="0.7" />
</models>
</modelAsset>
</selectedModelAssets>
<savingModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<surplusModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<modelAssetTimeSeries>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.027353" />
<tValues t="2" value="0" annualYield="0.027288" />
<tValues t="3" value="0" annualYield="0.027237" />
<tValues t="4" value="0" annualYield="0.027274" />
<tValues t="5" value="0" annualYield="0.027262" />
<tValues t="6" value="0" annualYield="0.02722" />
<tValues t="7" value="1453" annualYield="0.027258" />
<tValues t="8" value="1457" annualYield="0.027258" />
<tValues t="9" value="1460" annualYield="0.027219" />
<tValues t="10" value="1463" annualYield="0.027259" />
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.065466" />
<tValues t="2" value="0" annualYield="0.063841" />
<tValues t="3" value="0" annualYield="0.063707" />
<tValues t="4" value="0" annualYield="0.063692" />
<tValues t="5" value="0" annualYield="0.062438" />
<tValues t="6" value="0" annualYield="0.064081" />
<tValues t="7" value="0" annualYield="0.063476" />
<tValues t="8" value="0" annualYield="0.064294" />
<tValues t="9" value="0" annualYield="0.062034" />
<tValues t="10" value="0" annualYield="0.065144" />
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978">
<tValues t="0" value="830" annualYield="0" />
<tValues t="1" value="1641" annualYield="0.06504" />
<tValues t="2" value="2456" annualYield="0.063229" />
<tValues t="3" value="3278" annualYield="0.062939" />
<tValues t="4" value="4104" annualYield="0.062825" />
<tValues t="5" value="4935" annualYield="0.061233" />
<tValues t="6" value="5772" annualYield="0.063522" />
<tValues t="7" value="5155" annualYield="0.062448" />
<tValues t="8" value="5994" annualYield="0.063548" />
<tValues t="9" value="6837" annualYield="0.061053" />
<tValues t="10" value="7688" annualYield="0.06525" />
</modelAsset>
</modelAssetTimeSeries>
</ALMSimulationResult>
</ReportDocument>

When I run the following command from SQL server:

SELECT
N.ma.value('@.ID', 'uniqueidentifier') as ModelAssetID,
N.ma.value('@.description', 'nvarchar(255)') as ModelAssetDescription,
N.ma.value('@.minimumLimit', 'float') as ModelAssetMinLimit,
N.ma.value('@.maximumLimit', 'float') as ModelAssetMaxLimit,
N.ma.value('@.stochasticDuration', 'float') as ModelAssetDuration,
N.ma.value('@.color', 'char(9)') as Color,
N1.ma1.value('tValues[1]/@.value', 'float') as ActualAssetMix
FROM ALMPayloads A1 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)
LEFT JOIN ALMPayloads A2 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)
ON N.ma.value('@.ID', 'uniqueidentifier') = N1.ma1.value('@.ID', 'uniqueidentifier')
WHERE A1.ID = 1000
ORDER BY ModelAssetDuration

I get the following result:

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 96803
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Instead of

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

Are you sure there is not other data in the table? I notice that you are doing a self join on the the table [ALMPayloads] but I don't see any predicate on the A2 table alias. I suspect that this could be the reason why you are seeing the additional rows in your results if in fact there are other rows in that table.|||Hi John,
try to add another record (the same xml content with 2 different IDs) and the result will be duplicated.

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

ALMPayloads A1
CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)