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