CODEWITHSUNDEEP
xmlt-sqlxquery
user1077876
user1077876

Reputation: 11

xml.value in TSQL only returns 1 record

I have a short 2 record XML text, but when I run .value I only get the first record. How do I get a table list of only the values?

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

Results:

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

Upvotes: 1

Views: 2337

Answers (2)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

Use:

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

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

Try this (see this question for more information):

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 returns a single value, so you need to query from a collection of nodes to get multiple results.

Upvotes: 1

Related Questions