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

Question

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....

Answer 1

You can use [contains(local-name(.),'Number')]' demo to find elements whose name contains the string Number

Declare @x xml = '<Path>
    <To>
        <Value>
            <Array>
                <NumberDecimal>10.0</NumberDecimal>
                <TextEnglish>Ten</TextEnglish>
                <NumberRomanNumeral>X</NumberRomanNumeral>
            </Array>
        </Value>
    </To>
</Path>'


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

or [substring(local-name(.),1,6) eq "Number"] to find elements where the string is in a certain place (in this case the start)

For anything more exotic you are probably best off doing it in TSQL

WHERE child.value('local-name(.)', 'sysname')  LIKE '[SomeExpression]'