How to filter out unwanted nodes?

I would like to retrieve the values of ProductIDs nodes but the condition is to ignore any ProductIDs that is a child of <AlternativeState> node.

Please keep in mind that xml data comes from table and the value of ProductIDs could appear anywhere else as well so I am afraid using exact XPath is not a choice. The only time when ProductIDs is not returned when its a child of AlternativeState.

Currently I have something like this but it returns all of the productIDs from the xml instance

SELECT UsageID, xmlState.query('

for $productIDs in //*/ProductIDs

order by $productIDs

return string($productIDs)') AS ProductIDs

FROM Usage.UsageAnalysis

WHERE UsageID = 91923

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<ProductIDs>46420</ProductIDs>

<AlternativeState><!-- Ignore anything in this element-->

<OtherEvents>

<ProductIDs>46420</ProductIDs>

</OtherEvents>

<MarketSize>

<ProductIDs>46431,46440,46469,46470</ProductIDs>

</MarketSize>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<ProductIDs>2128,2459,46420,46440,46470</ProductIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

In your sample XML the AlternativeState element does not have a ProductIDs child, only a grand child. So for that XML sample the expression

Code Snippet

for $productIDs in //*[not(self::AlternativeState)]/*/ProductIDs

order by $productIDs

return string($productIDs)

should filter out what you want to filter out. Using an ancestor axis test should solve the problem in general but that axis is not currently supported in XQuery for SQL Server 2005.