1

I am new to XML using SQL Server 2008 R2, I have a xml data column named EventData the xml is structured like this...

<eventData>
  <Name> Mr Blog</Name>
  <Description> Hello World />
  <Date>10/06/2013</Date> 
</eventData>

What I require is each child element of eventData to be in a table stored like this

Key           Value
Name          Mr Blog
Description   Hello World
Date          10/06/2013

I read this previous question but does not solve my issue as my child elements are dynamic and they will not be the same each time.

Any ideas as to how I can structure my query to achieve this?

Community
  • 1
  • 1
LiamM
  • 13
  • 1
  • 4
  • what do you mean by word Dynamic in this context - are the name of columns are not static or no of columns are not fixed and they very ? – Pawan Jun 14 '13 at 09:36
  • The Column names and values can be different each time the query is run, the number of columns and the column names and their values will vary. – LiamM Jun 14 '13 at 10:32

1 Answers1

1

How about this?

DECLARE @input XML = '<eventData>
  <Name> Mr Blog</Name>
  <Description> Hello World </Description>
  <Date>10/06/2013</Date> 
</eventData>'

SELECT
    Name = XNodes.value('local-name(.)', 'varchar(100)'),
    VALUE = XNodes.value('(.)[1]', 'varchar(200)')
FROM @input.nodes('/eventData/*') AS XTbl(XNodes)

This gives me an output of:

enter image description here

and it's completely dynamic in that it will list of whatever subnodes your have under <eventData> and their value.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    I thought I would add that as the data was from a data table, I used a CROSS APPLY to read the data from the XML column rather than declaring a variable. – LiamM Jun 14 '13 at 11:50