Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
SSIS 7
xml 3
dynamic 3
Union All 3
SSRS 2
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

Convert XML data to sql table data

Jun 6 2013 12:00AM by Divya Agrawal Shah   

Few days back, I came across a situation in which I need to import my table data from SSIS into XML file. I did that part, now I have to do enough testing that whether the XML generated was correct or not. The XML contains thousands of nodes and checking each node one by one was a pain, and even the structure of XML was custom and having hundred’s of columns, so cannot easily convert into table format.

I searched about it and created one generic solution which will convert the XML into a table in sql server and then it will be easy to join the table with source and do testing.

Let us run an example to demonstrate the solution.

I have created a TestData table, in which all the XML file data will be saved, it will behave as an audit table, where the history is logged.

 

CREATE TABLE TestData (RowID int, XMLdata XML)

I am inserting a test xml row created from sysobjects for testing.

INSERT INTO dbo.TestData(RowID,XMLdata)
SELECT 1,
			(SELECT name as "name" ,
					object_id as "objectid",
					schema_id as "schemaid",
					type  as "objecttype"
			FROM sys.objects
			FOR XML PATH ('Objects'), ROOT('AllObjects'))   
Now, once the data is inserted let us start coverting it row by row into a table

I have used Xquery  local-name function to fetch the node name value, since there are 100’s of nodes inside the xml, it’s a generic method. Now, each node should have a unique identifier to identify the record. Here, I am having Objectid as a Unique identifier node, which can be used to identify a row. Since this will be totally unpivoted data, we need one identification column, and objectid will be the same.

   

SELECT  DISTINCT
		a.RowID,
		f.x.value('(objectid)[1]', 'varchar(max)')  AS ObjectID,
		t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,
		t.c.value('local-name(.)', 'varchar(max)') AS NodeName,        
		t.c.value('(.)[1]', 'varchar(max)') AS NodeText
		INTO #TempNodeValues
FROM   TestData a
CROSS APPLY a.XMLdata.nodes('//AllObjects/Objects') AS   f(x)
CROSS APPLY f.x.nodes('./*') AS   t(c)


Insert the data of the above query in some Temp table. I am inserting in #TempNodeValues temp table.

Next step is to PIVOT the data so that all the columns comes up in one single row for one objectid. So, do a distinct nodename from the temp table

  
SELECT DISTINCT NodeName from #TempNodeValues

 Output is four values which we have used while creating the XML

NodeName

------------
objectid
name
objecttype
schemaid

Now all the major work is completed. We just need to pivot the data. In the query below paste the distinct nodenames in the Pivot section. Since we have taken objectid as a separate column in the hash table we will ignore it now. 

SELECT rowid, objectid,name,objecttype,schemaid
FROM (SELECT *  FROM #TempNodeValues )T
PIVOT
( 
	MIN(T.NODETEXT) FOR NODENAME IN (name,objecttype,schemaid)
)PVT

  

Run the query, and all the XML is now converted to table. Here, only two place we need to do a manual work, otherwise it is generic to every problem.

Let me know your views on the post and If anyone has implemented the solution in some other way, please share with me.

Tags: 


Divya Agrawal Shah
89 · 2% · 629
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Convert XML data to sql table data" rated 5 out of 5 by 2 readers
Convert XML data to sql table data , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]