TSQL XML - 在Cross Apply上过滤节点LIKE'x'

With the following XML:

使用以下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?

如何过滤以下交叉应用于所有/唯一的Number *** xml节点?

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 个解决方案

#1

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

您可以使用[contains(local-name(。),'Number')]'demo来查找名称中包含字符串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)

或[substring(local-name(。),1,6)eq“Number”]查找字符串在某个位置的元素(在本例中为start)

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

对于任何更奇特的东西,你可能最好在TSQL中做到这一点

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

#1