SQL XML Column's Elements to separate columns

Question

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.

Gottfried Lesigang · Accepted Answer · 2016-03-24 12:47:25Z

3

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;

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

edited Mar 24, 2016 at 12:47

answered Mar 24, 2016 at 10:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Philip Over a year ago

Thank you, I don't think that works as the data is meaningless without the element name unfortunately. In reply to your questions: yes there is always a root element data. There are only 1 level of elements, with no attributes. There isn't a maximum number, unfortunately, though i would imagine more than 100. The resulting view would be used for reporting. It is possible to define new elements from a users point of view, but they don't have access to SQL to update the view. Hence trying to create something dynamic.

Gottfried Lesigang Over a year ago

@Philip, did you check the dynamic approach (see my EDIT)?

Gottfried Lesigang Over a year ago

@Philip, I just added brackets to the alias name for the dyamic approach. Might be, that element's names contain a minus or other forbidden characters...

Philip Over a year ago

I hadn't seen the edit sorry, that returns the values that i am after however it isn't possible to use your query in a view.

Gottfried Lesigang Over a year ago

@Philip, true, that is one of the biggest draw backs of dynamic SQL. You cannot use them in ad-hoc VIEWS and also not in functions. But there is no other way to create output column names dynamically...

|

Collectives™ on Stack Overflow

SQL XML Column's Elements to separate columns

1 Answer 1

EDIT

EDIT 2 - use this to create a VIEW

previous

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

EDIT 2 - use this to create a VIEW

previous

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related