9

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.

MiMo
  • 11,793
  • 1
  • 33
  • 48
nitewulf50
  • 530
  • 1
  • 4
  • 17

1 Answers1

5

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

Community
  • 1
  • 1
MiMo
  • 11,793
  • 1
  • 33
  • 48
  • I have no idea how that works but it does - perfecly. And intellisense in SQL Managment Studio doesn't either. It's confused about item.value and data.nodes. I must go research the nodes method. Thank you so much! – nitewulf50 Apr 26 '13 at 23:58