TSQL中的xml.value仅返回1条记录

Question

我有一个很短的两条记录的XML文本，但是当我运行.value时，我只得到第一条记录。如何获取仅包含值的表列表？

declare @x as xml

set @x = '<?xml version="1.0" encoding="UTF-8"?>
          <vehicle> 
              <vehicleid>54142</vehicleid> 
              <networkid>21301</networkid> 
              <description>Demo #54143</description>
          </vehicle>
          <vehicle> 
              <vehicleid>54143</vehicleid> 
              <networkid>213101</networkid> 
              <description>Demo #54143</description>
          </vehicle> '

SELECT @x.query('/*')

SELECT @x.value('(/vehicle/vehicleid/text())[1]', 'varchar(50)') as vehicleid, 
  @x.value('(/vehicle/description/text())[1]', 'varchar(50)') as description 

go

结果：

<vehicle><vehicleid>54142</vehicleid><networkid>21301</networkid><description>Demo #54143</description></vehicle> (WRAP to next line)
<vehicle><vehicleid>54143</vehicleid><networkid>213101</networkid><description>Demo #54143</description></vehicle>

(1 row(s) affected)

vehicleid                                          description
54142                                              Demo #54143

(1 row(s) affected)

Answer 1

试试这个(有关详细信息，请参阅 )：

SELECT T.C.value('(vehicleid/text())[1]', 'varchar(50)') as vehicleid, 
  T.C.value('(description/text())[1]', 'varchar(50)') as description
from @x.nodes('//vehicle') as T(C)

@x.value返回单个值，因此您需要从节点集合中进行查询才能获得多个结果。

Answer 2

使用：

SELECT t.c.value('(vehicleid)[1]', 'varchar(50)') [vehicleid]
    , t.c.value('(description)[1]', 'varchar(50)') [description]
FROM @x.nodes('//vehicle') t(c)