CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2012
Philip
Philip

Reputation: 85

SQL XML Column's Elements to separate columns

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.

Upvotes: 3

Views: 1429

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

EDIT

If you need a fully generic approach you might try dynamic SQL:

DECLARE @cmd VARCHAR(1000)=
'select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM #temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from #temp;'
EXEC (@cmd);

EDIT 2 - use this to create a VIEW

Views cannot use temp tables, had to change your #temp to a normal table...

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>')

DECLARE @cmd VARCHAR(1000)=
'CREATE VIEW dbo.SomeName AS select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from temp;'
EXEC (@cmd);
GO

SELECT * FROM dbo.SomeName;
GO

drop view dbo.SomeName;
drop table temp;

previous

The problem with XML is: You have to know the structure, at least some things your data has in common: Is there always a root element "data"? Are there always 1:n inner elements and nothing else? What is their maximum number? How would you know which element is missing, if you have a and c but no b?

This would be one approach:

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

You would get the same if you know the inner element's names by querying with

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

The result

Id  Name    a       b       c
1   Fred    Red     Apple   NULL
2   Mary    Blue    Ball    NULL
3   Paul    Green   Tree    NULL
4   Lisa    Yellow  Hat     House

Upvotes: 3

Related Questions