在SQL中从XML中选择值

时间：2021-07-11 18:06:39

I need to select values from an XML document. These values are stored as childnodes as follows:

我需要从XML文档中选择值。这些值作为子节点存储如下:

   <customers>
        <customer>
            <kunnr>1</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
        <customer>
            <kunnr>2</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
    </customers>

I need to select the values of kunnr and kdgrp for every customer node. I expect a result like this:

我需要为每个客户节点选择kunnr和kdgrp的值。我希望会有这样的结果:

kunnr       kdgrp
1           2
2           2

What I tried so far:

到目前为止我所尝试的:

SELECT  @xml.query('/customers/customer/kunnr') AS KUNNR,
        @xml.query('/customers/customer/kdgrp') AS KDGRP

This results in one row with two colums containing XML:

这导致在一行中包含两个包含XML的列:

KUNNR                                     KDGRP
<kunnr>1</kunnr><kunnr>2</kunnr>          <kdgrp>2</kdgrp><kdgrp>2</kdgrp>

Another try:

另一个尝试:

SELECT  C.value('/kunnr/text()','nvarchar(10)') as KUNNR,
        C.value('/kdgrp/text()','nvarchar(10)') as KDGRP
from @xml.nodes('/customers/customer') AS T(C);

This resulted in the following error message:

这导致了以下错误消息:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

2 个解决方案

#1

Maybe something like this:

也许是这样的:

DECLARE @xml XML
SET @xml='<customers>
        <customer>
            <kunnr>1</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
        <customer>
            <kunnr>2</kunnr>
            <kdgrp>2</kdgrp>
        </customer>
    </customers>'

And then a query like this:

然后是这样的查询:

SELECT
    c.value('kunnr[1]', 'nvarchar(10)') AS kunnr,
    c.value('kdgrp[1]', 'nvarchar(10)') AS kdgrp
FROM
    @xml.nodes('//customers/customer') as t(c)

This will give you this result:

这会给你一个结果:

kunnr  kdgrp
1      2
2      2

#2

I had a problem related to extracting values from T-SQL XML and found an issue that may help others. When retrieving data with: .value('(/root/subnode)[1]', 'varchar(max)') that call would not retrieve data but the following call did: .value('(//subnode)[1]', 'varchar(max)'). Note that the working version replaced the root node with a /. The problem with the first call seemed to be that the root node came with a specification of an xml namespace like &< root xmlns="http://www..." &> and to get the .value call to return data I needed to get past the specification of the namespace which was causing things to fail for some reason.

我遇到了一个从T-SQL XML中提取值的问题，并且发现了一个可以帮助其他人的问题。当使用:.value('(/root/subnode)[1]'、'varchar(max)'检索数据时，该调用不会检索数据，但以下调用会检索:.value('(//subnode)[1]'、'varchar(max)')。注意，工作版本用/替换了根节点。第一次调用的问题似乎是根节点提供了xml名称空间规范，如&< root xmlns="http://www.. "。并且获取.value调用来返回我需要的返回的数据，这是由于某些原因导致的事情失败的命名空间的规范。

#1