Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

GO

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

')

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')

)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

')

/*

Result set from your query:

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

<CountryCodes>BR</CountryCodes>

<CountryCodes>FR</CountryCodes>

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

*/

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

/*

KT,LC,VG,SU,TT,UY,VE

BR

FR

*/

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help