Using SQL to convert / smooth XML structure to columns

Question

I am using SQL Server (2008/2012) and I know that there are similar answers from a large number of queries, however I cannot find a suitable example / pointers for my case.

I have an XML column in a SQL Server table containing this data:

<Items> <Item> <FormItem> <Text>FirstName</Text> <Value>My First Name</Value> </FormItem> <FormItem> <Text>LastName</Text> <Value>My Last Name</Value> </FormItem> <FormItem> <Text>Age</Text> <Value>39</Value> </FormItem> </Item> <Item> <FormItem> <Text>FirstName</Text> <Value>My First Name 2</Value> </FormItem> <FormItem> <Text>LastName</Text> <Value>My Last Name 2</Value> </FormItem> <FormItem> <Text>Age</Text> <Value>40</Value> </FormItem> </Item> </Items> 

So, although the <FormItem> structure will be the same, I can have many (most often no more than 20-30) sets of form elements.

I am essentially trying to return a query from SQL in the following format, i.e. dynamic columns based on / FormItem / Text:

 FirstName LastName Age ---> More columns as new `<FormItem>` are returned My First Name My Last Name 39 Whatever value etc.. My First Name 2 My Last Name 2 40 

So, at the moment I had the following:

 select Tab.Col.value('Text[1]','nvarchar(100)') as Question, Tab.Col.value('Value[1]','nvarchar(100)') as Answer from @Questions.nodes('/Items/Item/FormItem') Tab(Col) 

Of course, this did not move my XML rows into columns and, obviously, is still fixed by fields. I tried to use different approaches of "Dynamic SQL", where SQL makes an excellent choice (in my case) of a <Text> node and then uses some kind of code? but I could not find a magic combination to return the results that I needed, as a dynamic set of columns for each row ( <Item> in the <Items> collection).

I am sure that this can be done by seeing so many very similar examples, but again the solution eludes me!

Any help gratefully received !!

Answer 1

XML parsing is quite expensive, so instead of parsing it once to create a dynamic query and once to get the data, you can create a temporary table called Name-Value, and then use this as a source for the dynamic summary query.
dense_rank exists to create an identifier to rotate around.
To create a list of columns in a dynamic query, it uses the for xml path('') trick.

This solution requires your table to have a primary key (ID). If you have XML in a variable, it can be somewhat simplified.

 select dense_rank() over(order by ID, IN) as ID, FNvalue('(Text/text())[1]', 'varchar(max)') as Name, FNvalue('(Value/text())[1]', 'varchar(max)') as Value into #T from YourTable as T cross apply T.XMLCol.nodes('/Items/Item') as I(N) cross apply INnodes('FormItem') as F(N) declare @SQL nvarchar(max) declare @Col nvarchar(max) select @Col = ( select distinct ','+quotename(Name) from #T for xml path(''), type ).value('substring(text()[1], 2)', 'nvarchar(max)') set @SQL = 'select ' +@Col +' from #T pivot (max(Value) for Name in (' +@Col +')) as P' exec (@SQL) drop table #T 

SQL Fiddle

Answer 2

 select Tab.Col.value('(FormItem[Text = "FirstName"]/Value)[1]', 'varchar(32)') as FirstName, Tab.Col.value('(FormItem[Text = "LastName"]/Value)[1]', 'varchar(32)') as LastName, Tab.Col.value('(FormItem[Text = "Age"]/Value)[1]', 'int') as Age from @Questions.nodes('/Items/Item') Tab(Col) 

Answer 3

I wanted to add my “own answer” really just for completeness, to possibly help others. However, this is definitely based on the great help from @Mikael above! so again, this is really for completeness - all this is attached by @Mikael.

I basically ended up with the following proc. I needed to select some data / filter and get some combined data and enable some Boolean filtering on some input parameters. Then go to the next section, which created a temporary table of my relational data, and the required xml nodes through the cross are applied. The final step was to then collapse the results / dynamically create columns from the selected XML node ..

 CREATE PROCEDURE [dbo].[usp_RPT_ExtractFlattenentries] @CompanyID int, @MainSelector nvarchar(50) = null, @SecondarySelector nvarchar(255) = null, @DateFrom datetime = '01-jan-2012', @DateTo datetime = '31-dec-2100', @SysReference nvarchar(20) = null AS BEGIN SET NOCOUNT ON; -- Create the table var to hold the XML form data from the entries declare @FeedbackXml table ( ID int identity primary key, XMLCol xml, CompanyName nvarchar(20), SysReference nvarchar(20), RecordDate datetime, EntryName nvarchar(255), MainSelector nvarchar(50) ) -- STEP 1: Get the raw submission data based on the params passed in -- *Note: The double casting is necessary as the "form" field is nvarchar (not varchar) and we need xml in UTF-8 format begin insert into @FeedbackXml (XMLCol, CompanyName, SysReference, RecordDate, EntryName, MainSelector) select cast(cast(e.form as nvarchar(max)) as xml), c.name, e.SysReference, e.RecordDate, e.name, e.wizard from entries s left join companies o on e.companies = c.ID where (@CompanyID = -1 or @CompanyID = e.companies) and (@MainSelector is null or @MainSelector = e.wizard) and (@SecondarySelector is null or @SecondarySelector = e.name) and (@SysReference is null or @SysReference = e.SysReference) and (e.RecordDate >= @DateFrom and e.RecordDate <= @DateTo) end -- STEP 2: Flatten the required XML structure to provide a base for the pivot, and include other fields we wish to output select dense_rank() over(order by ID) as ID, T.RecordDate, T.CompanyName, T.SysReference, T.EntryName, T.MainSelector, FNvalue('(FieldNameNode/text())[1]', 'nvarchar(max)') as FieldName, FNvalue('(FieldNameValue/text())[1]', 'nvarchar(max)') as FieldValue into #TempData from @FeedbackXml as T cross apply T.XMLCol.nodes('/root/companies/') as I(N) -- Xpath to the desired node start point cross apply INnodes('company') as F(N) -- The actual node collection that forms the "field name" and "field value" data -- STEP 3: Pivot the #TempData table creating a dynamic column structure based on the selected XML nodes in step 2 declare @SQL nvarchar(max) declare @Col nvarchar(max) select @Col = ( select distinct ','+quotename(FieldName) from #TempData for xml path(''), type ).value('substring(text()[1], 2)', 'nvarchar(max)') set @SQL = 'select CompanyName, SysReference, EntryName, MainSelector, RecordDate, ' +@Col +' from #TempData pivot (max(FieldValue) for FieldName in (' +@Col +')) as P' exec (@SQL) drop table #TempData END 

Again, in fact, only added this answer to present the full picture from my point of view, and may help others.