January 13, 2014 at 12:14 am
Hi
I need to create a proc which will get @xml as input parameter and it has to return the given XML as a table. The problem I am facing is that the structure of XML will keep changing. Only first 2 nodes will be same in all XML and rest will keep changing. So I guess I need to pick the nodes dynamically but I am not sure how that can be done. Any help is appreciated.
Here are 2 of my sample XML:
XML1:
'<data>
<row id="1">
<Name>Test1-L</Name>
<FirstName>Test1-F</FirstName>
<Phone>Germany</Phone>
<Street>Germany</Street>
<Country>Germany
</Country>
</row>
<row id="2">
<Name>Test2-L</Name>
<FirstName>
Test2-F</FirstName>
<Phone>Germany</Phone>
<Street>Germany
</Street>
<Country>Germany</Country>
</row>
</data>'
XML2:
<data>
<row id="1">
<CarName>Test1-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
<row id="2">
<CarName>Test2-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
<row id="3">
<CarName>Test3-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
</data>
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
January 13, 2014 at 6:37 am
This is going to need a lot of testing. Also, I'm not sure that it's the best way to go about this sort of problem. I've written this more as an intellectual exercise in my lunch break than anything else.
--EDIT-- Don't use this version, I've made a couple of minor corrections in the next post.
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('xml_to_table') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN;
DROP PROCEDURE [dbo].[xml_to_table];
END;
GO
CREATE PROCEDURE [dbo].[xml_to_table] (@XML XML) AS
BEGIN;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT
name,value,id,CASE WHEN value IS NULL THEN 1 ELSE 0 END AS [group]
INTO #testEnvironment
FROM (SELECT
xmlData.value('local-name(.)', 'nvarchar(max)'),
xmlData.value('(./text())[1]', 'nvarchar(max)'),
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @XML.nodes('//*') [XML](xmlData)
)a(name,value,id)
OPTION (MAXDOP 1,RECOMPILE);
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL =
'SELECT'+SPACE(1)+columnNames+CHAR(13)+CHAR(10)+'FROM ('+
'SELECT [group],'+SPACE(1)+CHAR(13)+CHAR(10)+caseStmt+CHAR(13)+CHAR(10)+SPACE(6)+
'FROM (SELECT name,value,id,'+CHAR(13)+CHAR(10)+SPACE(12)+
'ROW_NUMBER() OVER(ORDER BY id)-'+CHAR(13)+CHAR(10)+SPACE(12)+
'DENSE_RANK() OVER(PARTITION BY [group] ORDER BY id) AS [group]'+CHAR(13)+CHAR(10)+SPACE(12)+
'FROM #testEnvironment '+CHAR(13)+CHAR(10)+SPACE(11)+
')a'+CHAR(13)+CHAR(10)+SPACE(6)+
'WHERE value IS NOT NULL'+CHAR(13)+CHAR(10)+SPACE(6)+
'GROUP BY [group]'+CHAR(13)+CHAR(10)+SPACE(6)+
')a;'
FROM (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+SPACE(6)+
'MAX(CASE WHEN name ='+SPACE(1)+CHAR(39)+name+CHAR(39)+SPACE(1)+'THEN value END) AS'+SPACE(1)+QUOTENAME(name)
FROM (SELECT name, MIN(id) AS id
FROM #testEnvironment
WHERE value IS NOT NULL
GROUP BY name
)a
ORDER BY id
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,3,'')
)a(caseStmt)
CROSS APPLY (SELECT STUFF((SELECT ','+QUOTENAME(name)
FROM (SELECT name, MIN(id) AS id
FROM #testEnvironment
WHERE value IS NOT NULL
GROUP BY name
)a
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
)b(columnNames);
EXECUTE sp_executesql @SQL;
END;
GO
Here's the tests: -
DECLARE @XML XML = '<data>
<row id="1">
<Name>Test1-L</Name>
<FirstName>Test1-F</FirstName>
<Phone>Germany</Phone>
<Street>Germany</Street>
<Country>Germany
</Country>
</row>
<row id="2">
<Name>Test2-L</Name>
<FirstName>
Test2-F</FirstName>
<Phone>Germany</Phone>
<Street>Germany</Street>
<Country>Germany</Country>
</row>
</data>';
EXECUTE [dbo].[xml_to_table] @XML;
GO
DECLARE @XML XML = '<data>
<row id="1">
<CarName>Test1-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
<row id="2">
<CarName>Test2-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
<row id="3">
<CarName>Test3-L</CarName>
<Nr>SI-123-TJ</Nr>
<Type>Mercedes</Type>
</row>
</data>';
EXECUTE [dbo].[xml_to_table] @XML;
Returns: -
Country FirstName Name Phone Street
----------------------------------------------------
Germany Test1-F Test1-L Germany Germany
Germany Test2-F Test2-L Germany Germany
Warning: Null value is eliminated by an aggregate or other SET operation.
(2 row(s) affected)
CarName Nr Type
---------------------------------
Test1-L SI-123-TJ Mercedes
Test2-L SI-123-TJ Mercedes
Test3-L SI-123-TJ Mercedes
Warning: Null value is eliminated by an aggregate or other SET operation.
(3 row(s) affected)
January 13, 2014 at 6:42 am
Couple of minor corrections here.
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('xml_to_table') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
BEGIN;
DROP PROCEDURE [dbo].[xml_to_table];
END;
GO
CREATE PROCEDURE [dbo].[xml_to_table] (@XML XML) AS
BEGIN;
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT
name,value,id,CASE WHEN value IS NULL THEN 1 ELSE 0 END AS [group]
INTO #testEnvironment
FROM (SELECT
xmlData.value('local-name(.)', 'nvarchar(max)'),
xmlData.value('(./text())[1]', 'nvarchar(max)'),
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @XML.nodes('//*') [XML](xmlData)
)a(name,value,id)
OPTION (MAXDOP 1,RECOMPILE);
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL =
'SELECT'+SPACE(1)+columnNames+CHAR(13)+CHAR(10)+'FROM ('+
'SELECT [group],'+SPACE(1)+CHAR(13)+CHAR(10)+caseStmt+CHAR(13)+CHAR(10)+SPACE(6)+
'FROM (SELECT name,value,id,'+CHAR(13)+CHAR(10)+SPACE(12)+
'ROW_NUMBER() OVER(ORDER BY id)-'+CHAR(13)+CHAR(10)+SPACE(12)+
'DENSE_RANK() OVER(PARTITION BY [group] ORDER BY id) AS [group]'+CHAR(13)+CHAR(10)+SPACE(12)+
'FROM #testEnvironment '+CHAR(13)+CHAR(10)+SPACE(11)+
')a'+CHAR(13)+CHAR(10)+SPACE(6)+
'WHERE value IS NOT NULL'+CHAR(13)+CHAR(10)+SPACE(6)+
'GROUP BY [group]'+CHAR(13)+CHAR(10)+SPACE(6)+
')a;'
FROM (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+SPACE(6)+
'MAX(CASE WHEN name ='+SPACE(1)+CHAR(39)+name+CHAR(39)+SPACE(1)+'THEN value END) AS'+SPACE(1)+QUOTENAME(name)
FROM (SELECT name, MIN(id) AS id
FROM #testEnvironment
WHERE value IS NOT NULL
GROUP BY name
)a
ORDER BY id
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,3,'')
)a(caseStmt)
CROSS APPLY (SELECT STUFF((SELECT ','+QUOTENAME(name)
FROM (SELECT name, MIN(id) AS id
FROM #testEnvironment
WHERE value IS NOT NULL
GROUP BY name
)a
ORDER BY id
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
)b(columnNames);
EXECUTE sp_executesql @SQL;
END;
GO
January 13, 2014 at 6:51 am
I can't thanks more. Works as i needed.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
January 13, 2014 at 6:54 am
Make sure that you understand it. You've got to support the code, not me 😉
January 13, 2014 at 7:13 am
ya, trying to;-)
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply