Select through xml query

Question

I have

declare @xml_var varchar(4000) 
set  @xml_var =  
'<Text  ID = "1">
<Good Number ="Number_0"/>
<Good Number ="Number_1"/>
<Good Number ="Number_2"/>
<Good Number ="Number_3"/>
</Text>' 

I try to do a script that returns non xml output like this

Number_0
Number_1
Number_2
Number_3

How can I do it?

I try

SELECT 
good.value('@Number', 'nvarchar(50)') as Number
FROM @xml_var.nodes('/Text') col(Text)

I do not have access to sql server now, that would be later

OK, I have solution but when I try

declare @string varchar(8000)
declare @xml_var xml;  
set  @string =  
'<Text  ID = "1">
<Good Number ="Number_0"/>
<Good Number ="Number_1"/>
<Good Number ="Number_2"/>
<Good Number ="Number_3"/>
</Text>'
SET @xml_var = @string;
SELECT Data.Col.value('@Number', 'nvarchar(100)') AS Number
FROM @xml_var.nodes('/Text/Good') AS Data(Col)

there on sql fiddle error appears

Answer 1

First of all, you have to declare your variable/column as XML instead of VARCHAR.

Then your query should look like that:

SELECT T.N.value('@Number', 'nvarchar(100)') AS Number
FROM @xml_var.nodes('/Text/Good') AS T(N)

Working DEMO. You can use SQL Fiddle to test queries like that one when you don't have access to your database.