Select all XML attributes in SQL

Question

I have an XML value list contained in a SQL Table Field that looks like so:

<valuelist xmlns="" name="VL_IncCompCondVL">
  <li value="BL" item="BLOCKED" />
  <li value="NK" item="NO KEY" />
  <li value="FL" item="FLOODED" />
  <li value="TD" item="TORN DOWN" />
  <li value="UL" item="UNABLE TO LOCATE" />
</valuelist>

I want to be able to create a temp SQL table like so:

CREATE TABLE #incompleteCode
(
value nvarchar(2),
item nvarchar(20)
)

and populate it with all the values/items from the XML so that I can use the temp table to JOIN with another SELECT statement.

SELECT Data.value('(/valuelist/li/@item)[1]', 'nvarchar(50)') AS Item
                                                    FROM ValueList
                                                    WHERE Name = 'VL_IncCompCondVL'

That statement gets me the first one, and if i increment [1] to [2] and so on, i can 1 by 1 select each attribute. But I have to believe there's a way to just get them all. I've tried some variations and am just not figuring it out. I think I need to use the * wildcard somewhere.

Answer 1

You should use the nodes method:

SELECT 
  item.value('.', 'nvarchar(50)') 
FROM 
  ValueList 
  CROSS APPLY data.nodes('/valuelist/li/@item') as T2(item) 
where 
  name='VL_IncCompCondVL'

See here about CROSS APPLY