How to get all values from this XML in SQL

Question

Im trying to figure out how to get all the string values from some XML passed into a SQL query and I cant figure this out, even after looking at a few examples. Can anyone point out where I have gone wrong? Thanks

My XML is:

DECLARE @CrewXML XML = N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <string>A</string>
  <string>E</string>
  <string>J</string>
</ArrayOfString>'

And so far I have this as my code:

select 
    t.ArrayOfString.value('string[1]','varchar(max)')
    from 
    @CrewXML.nodes('/ArrayOfString') as t(ArrayOfString)

This only gets the first row of the XML which I guess is due to the [1] however I cant seem to find a way to get all the values within the element.

Ideally this will return one column with multiple rows, one row for each entry in the XML.

Mikael Eriksson · Accepted Answer · 2012-02-02 14:17:44Z

6

Add string to the nodes function and get the value from the current node using ..

select 
    t.ArrayOfString.value('.','varchar(max)')
    from 
    @CrewXML.nodes('/ArrayOfString/string') as t(ArrayOfString)

answered Feb 2, 2012 at 14:17

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get all values from this XML in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related