Select all XML attributes in SQL

Question

I have a list of XML values ​​contained in an SQL table field that looks like this:

<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 create a temporary SQL table like this:

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

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

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

This operator gets me first, and if I increment [1] to [2] etc., I can select one on one each attribute. But I have to believe that there is a way to get them all. I tried some options and just don't understand. I think I need to use a wildcard.

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