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 name as "name" ,
object_id as "objectid",
schema_id as "schemaid",
type as "objecttype"
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.
f.x.value('(objectid)', '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('(.)', 'varchar(max)') AS NodeText
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
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
MIN(T.NODETEXT) FOR NODENAME IN (name,objecttype,schemaid)
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.