SQL XML Column's Elements to separate columns

Viewed 1279

I have a SQL table with an XML column. I would like to separate out the XML elements into their own columns within a view.

I am able to do this using .value, however I don't always know what the element names are. For example in the select below the c element is missing.

create table #temp (Id int, Name varchar(32), taskdata xml)

insert into #temp values
(1, 'Fred','<data><a>Red</a><b>Apple</b></data>'),
(2, 'Mary','<data><a>Blue</a><b>Ball</b></data>'),
(3, 'Paul','<data><a>Green</a><b>Tree</b></data>'),
(4, 'Lisa','<data><a>Yellow</a><b>Hat</b><c>House</c></data>')

select Id
      ,Name
      ,Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a
      ,Taskdata.value('(/data/b)[1]', 'nvarchar(max)') AS b
from #temp

drop table #temp

I can get a list of all the elements names using:

select distinct T.N.value('local-name(.)','nvarchar(64)') ColNames
from #temp
cross apply Taskdata.nodes('//data/*') as T(N)

However I cant work out how to replace:

Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a

For something more dynamic.

1 Answers
Related