TSQL XML - Filter Nodes LIKE 'x' on Cross Apply

Viewed 1109

With the following XML:

<Path>
    <To>
        <Value>
            <Array>
                <NumberDecimal>10.0</NumberDecimal>
                <TextEnglish>Ten</TextEnglish>
                <NumberRomanNumeral>X</NumberRomanNumeral>
            </Array>
        </Value>
    </To>
</Path>

How would I filter the following cross apply to all/only Number*** xml nodes?

SELECT child.value('concat(local-name(.),": ",.)', 'varchar(max)') AS [value]
FROM imports i
CROSS APPLY i.import_data.nodes('/Path/To/Value/Array/*[local-name(.) = ''NumberDecimal'']') AS nodes(child)

returns:

NumberDecimal: 10

Needs to be this:

SELECT child.value('concat(local-name(.),": ",.)', 'varchar(max)') AS [value]
FROM imports i
CROSS APPLY i.import_data.nodes('/Path/To/Value/Array/*[local-name(.) = ''Number/*'']') AS nodes(child)

Need to return:

NumberDecimal: 10

NumberRomanNumeral: X

But it returns nothing....

1 Answers
Related