Convert xml ntext in mssql to csv (ucomponents migration)

Question

I have lots of rows in an mssql table which are in xml format and I want to convert them to csv. The column is an ntext and the values are like.

<MultiNodePicker type="content">
  <nodeId>5713</nodeId>
  <nodeId>6817</nodeId>
  <nodeId>5424</nodeId>
  <nodeId>1888</nodeId>
  <nodeId>6128</nodeId>
  <nodeId>6339</nodeId>
</MultiNodePicker>

and I would like to convert them in 5713,6817,5424,1888,6128,6339 Any ideas how to achieve this with sql command?

** Probably this seems to be the way to go https://social.msdn.microsoft.com/Forums/sqlserver/en-US/780e80b2-f898-4451-b19f-332289fb1c8d/sql-xml-get-xml-values-into-csv?forum=sqlxml , save it in a function and then loop through all the rows and update the values. At the moment it is failing at x.y.value.

I tried also to use replace but it failed because it is xml and it doesnt get the space between the xml nodes.

I also used

set dataNvarchar = substring(CAST(dataNtext as nvarchar(500)), patindex('%[0-9]%', CAST(dataNtext as nvarchar(500))), 1+patindex('%[0-9][^0-9]%', CAST(dataNtext as nvarchar(500))+'x')-patindex('%[0-9]%', CAST(dataNtext as nvarchar(500))))

but it gets only the first numeric.

******* Additional Solution ********* These values come from uComponent plugin for Umbraco. I couldn't find a way to read it as xml - even though the solution to this as proper xml is correct. Instead I wrote this function which solved my problem

Create Function [dbo].[ParseXml](@text ntext) 
returns nvarchar(500) as 
Begin 
declare @strnvar nvarchar(MAX) -- main string to subtruct
declare @numberstr nvarchar(500) -- extracted number
declare @csvstr nvarchar(500) -- constructed csv string
-- Convert text to nvarchar
set @strnvar = CAST(@text as nvarchar(MAX))
set @csvstr = ''
set @numberstr = substring(@strnvar, patindex('%[0-9]%', @strnvar), 1 + patindex('%[0-9][^0-9]%', @strnvar+'x') - patindex('%[0-9]%', @strnvar))

while(DATALENGTH(@numberstr) > 0)
BEGIN 
set @csvstr = @csvstr + @numberstr + ','
set @strnvar = REPLACE(@strnvar, @numberstr, '')
set @numberstr = substring(@strnvar, patindex('%[0-9]%', @strnvar), 1 + patindex('%[0-9][^0-9]%', @strnvar+'x') - patindex('%[0-9]%', @strnvar))
END

IF RIGHT(@csvstr,1) = ',' 
    set @csvstr =  LEFT(@csvstr, LEN(@csvstr) - 1)
return @csvstr
end 
GO

Answer 1

If you convert ntext to xml then you can build comma separated as below

declare @x xml = '<MultiNodePicker type="content">
  <nodeId>5713</nodeId>
  <nodeId>6817</nodeId>
  <nodeId>5424</nodeId>
  <nodeId>1888</nodeId>
  <nodeId>6128</nodeId>
  <nodeId>6339</nodeId>
</MultiNodePicker>'

Select Stuff(( Select ','+ y.value(N'text()[1]', N'nvarchar(MAX)')
FROM @x.nodes(N'MultiNodePicker/nodeId') as x(y) for xml path('') ),1,1,'')

Output as below:

5713,6817,5424,1888,6128,6339

I created function as below and it is working fine:

Create Function [dbo].[ParseXml](@text ntext) returns nvarchar(500) as 
Begin declare @x xml = @text 
return 
Stuff(( Select ',' + y.value(N'text()[1]',N'nvarchar(MAX)') FROM @x.nodes(N'MultiNodePicker/nodeId') as x(y) for xml path('')),1,1,'') 
end 


declare @x nvarchar(500) = '<MultiNodePicker type="content">
  <nodeId>5713</nodeId>
  <nodeId>6817</nodeId>
  <nodeId>5424</nodeId>
  <nodeId>1888</nodeId>
  <nodeId>6128</nodeId>
  <nodeId>6339</nodeId>
</MultiNodePicker>'

select  dbo.ParseXml(@x)

Output : Same as above