Help needed with Xquery

Hello,

I'm trying to retreive the values from multiple nodes based on the value of another , without any success. The XML source is stored in an SQL(2005) xml column .

'Sample XML

<!--Combat Flight Sim mission-->

<Mission>

<Params Version="3.0" Directive="nothing" Country="Britain" Aircraft="p_51b" Airbase="brod23" Date="8/10/1940" Time="12:00" Weather="scatteredclouds3.xml" Multiplayer="y" MultiplayerOnly="n" />

.......

<AirFormation ID="6003" Directive="nothing" Country="Britain" Skill="1" FormType="diamond">

<Unit ID="9459" Type="p_51b" IsPlayer="y" Skill="1" />

<Unit ID="9460" Type="p_51b" Skill="2" />

.........

<AirFormation ID="6000" Directive="nothing" Country="Britain" Points="2" DamagePercent="40" Skill="2" Payload="2" FormType="box">

<Unit ID="9467" Type="b_25c" Skill="2" Payload="3" />

<Unit ID="9468" Type="b_25c" Skill="2" Payload="3" />

.........

AirFormation ID="6007" Directive="nothing" Country="Germany" Skill="2" FormType="fingertip">

<Unit ID="9475" Type="bf_109g_6" Skill="2" Payload="6" />

<Unit ID="9476" Type="bf_109g_6" Skill="2"

'This is the SQL code:

SELECT DISTINCT nref.value('@.Type', 'varchar(100)') Aircraft

FROM dbo.MOG_Missions CROSS APPLY xmlData.nodes('//AirFormation/Unit') as T(nref)

WHERE id = @.id 'some additional condition here is needed but I cannot figure it out

Which returns the following values from the ?Type attribute :

b_25c
bf_109g_6
p_51b

What I would like to accomplish is to return only the values from ?Type where the AirFormation-Country attribute matches the ?Country attribute of the ?Params node.

Thank you in advance.

Your XML sample is not clear to me. What is the relationship between the Params element and the AirFormation elements? If that is known then you should simply be able to express the condition in an XPath predicate in your nodes call. For example if the Params element is a sibling of the AirFormation elements then you can check e.g.

Code Snippet

SELECT DISTINCT t.u.value('@.Type', 'nvarchar(10)') AS Type

FROM example1

CROSS APPLY xml.nodes('//AirFormation[@.Country = ../Params/@.Country]/Unit') AS t(u)

WHERE id = 3;

|||I should have asked for help sooner! Thank you so much!