Import XML File and transform into Relational Model (for DYNAMIC Columns) using Powershell

Introduction

Importing XML data into the relational database is being supported by the introduction of OPENXML and OPENROWSET.

In the previous article, we have seen how to Import XML File and transform into Relational Model (for STATIC Columns ) using Powershell.

As promised, this article will focus on performing the same task but for DYNAMIC Columns

So let's start our journey

Environment Setup

Let us first use the below XML say(Employees.xml)

<Employees>
    <Employee EmpId="Emp001" EmpName="Rajlakshmi" DOJ="02/03/2010"/>   
    <Employee EmpId="Emp002" EmpName="Niladri" DOJ="10/08/2010"/>
    <Employee EmpId="Emp003" EmpName="Arina" DOJ="01/01/2010"/>
    <Employee EmpId="Emp004" EmpName="RNA Team" DOJ="31/12/2010"/>
</Employees>

Using the code

Open any text editor (save the file as "Employees.ps1") and let us write the following command

 [xml]$xml = Get-Content D:\Employees.xml
 $xml.Employees.Employee | Format-List

Now open the SSMS, fire the below query

DECLARE @command  VARCHAR(100)
SET @command = 'powershell.exe  D:\Employees.ps1'

--Create a Temporary table
CREATE TABLE #TblResultXML(EmpId INT IDENTITY(1,1), ResultData VARCHAR(1000) NULL)

--Using xp_cmdshell, invoke the PowerShell script and insert that into the Temporary table
INSERT INTO  #TblResultXML (ResultData) EXEC master..xp_cmdshell @command 

SELECT *
FROM #TblResultXML

--Drop the Temporary table
DROP TABLE #TblResultXML

So our records has been inserted

.Our first task is to seperate the Key and the Values.Let's modify our query as under

DECLARE @command  VARCHAR(100)
SET @command = 'powershell.exe  D:\Employees.ps1'
DECLARE @sql NVARCHAR(MAX)

--Create a Temporary table
CREATE TABLE #TblResultXML(EmpId INT IDENTITY(1,1), ResultData VARCHAR(1000) NULL)

--Using xp_cmdshell, invoke the PowerShell script and insert that into the Temporary table
INSERT INTO  #TblResultXML (ResultData) EXEC master..xp_cmdshell @command 

Select @sql = '
-- Seperate the Keys and Values
;WITH keyValuesSeperateCTE AS(
SELECT 
	EmpId
	, [Key] = LEFT(ResultData, CHARINDEX('':'', ResultData)-1)
	, [Value] = RIGHT(ResultData, LEN(ResultData) - CHARINDEX('':'', ResultData))
FROM #TblResultXML
WHERE ResultData IS NOT NULL AND EmpId > 2
)	
SELECT * FROM keyValuesSeperateCTE'
EXEC sp_executesql @sql

--Drop the Temporary table
DROP TABLE #TblResultXML

We are building our SQL query in dynamic way. @sql is a NVARCHAR Type variable.It is use to store the query string that we are building at runtime/dynamically.Finally, we are executing the dynamic T-SQL statement by using sp_executesql

Result:

For a cleaner approach, we are using Common Table Expression(CTE).The very next step is to Group the "Keys" as under

DECLARE @command  VARCHAR(100)
SET @command = 'powershell.exe  D:\Employees.ps1'
DECLARE @sql NVARCHAR(MAX)

--Create a Temporary table
CREATE TABLE #TblResultXML(EmpId INT IDENTITY(1,1), ResultData VARCHAR(1000) NULL)

--Using xp_cmdshell, invoke the PowerShell script and insert that into the Temporary table
INSERT INTO  #TblResultXML (ResultData) EXEC master..xp_cmdshell @command 

Select @sql = '
-- Seperate the Keys and Values
;WITH keyValuesSeperateCTE AS(
SELECT 
         EmpId
       , [Key] = LEFT(ResultData, CHARINDEX('':'', ResultData)-1)
	, [Value] = RIGHT(ResultData, LEN(ResultData) - CHARINDEX('':'', ResultData))
FROM #TblResultXML
WHERE ResultData IS NOT NULL AND EmpId > 2
),
--Group the Keys 
groupKeysCTE AS(

SELECT 
	Id = ROW_NUMBER() OVER(PARTITION BY X.[Key] ORDER BY X.EmpId)
	,[Key] 
	,[Value]
FROM KeyValuesSeperateCTE X
)	
SELECT * FROM groupKeysCTE'
EXEC sp_executesql @sql

--Drop the Temporary table
DROP TABLE #TblResultXML

Result:

Now we are very close.As a last step we only need to transform the rows to columns by using PIVOTING as shown under

DECLARE @command  VARCHAR(100)
SET @command = 'powershell.exe  D:\Employees.ps1'
DECLARE @sql NVARCHAR(MAX)

--Create a Temporary table
CREATE TABLE #TblResultXML(EmpId INT IDENTITY(1,1), ResultData VARCHAR(1000) NULL)

--Using xp_cmdshell, invoke the PowerShell script and insert that into the Temporary table
INSERT INTO  #TblResultXML (ResultData) EXEC master..xp_cmdshell @command 

Select @sql = '

-- Seperate the Keys and Values
;WITH keyValuesSeperateCTE AS(
SELECT 
	EmpId
	, [Key] = LEFT(ResultData, CHARINDEX('':'', ResultData)-1)
	, [Value] = RIGHT(ResultData, LEN(ResultData) - CHARINDEX('':'', ResultData))
FROM #TblResultXML
WHERE ResultData IS NOT NULL AND EmpId > 2
),
--Group the Keys 
groupKeysCTE AS(
SELECT 
     Id = ROW_NUMBER() OVER(PARTITION BY X.[Key] ORDER BY X.EmpId)
     ,[Key] 
     ,[Value]
FROM KeyValuesSeperateCTE X
)	
SELECT '+ LEFT(col, LEN(col)-1) +' 
FROM groupKeysCTE
PIVOT(
	MAX(Value)
	FOR [Key] IN('+LEFT(col, len(col)-1)+')
      )  AS PIV		'
FROM (
	SELECT DISTINCT '['+LEFT(ResultData, CHARINDEX(':', ResultData)-1)+'], '
	FROM #TblResultXML
	FOR XML PATH('')
     ) AS X(col)		  
EXEC sp_executesql @sql
--Drop the Temporary table
DROP TABLE #TblResultXML

Result:

For flattening the columns into a single row or variable, we are using the below query

SELECT DISTINCT '['+LEFT(ResultData, CHARINDEX(':', ResultData)-1)+'], '
FROM #TblResultXML
FOR XML PATH('')

Result:

FOR XML PATH concatenates multiple rows into a single column string.

The

SELECT  LEFT(col, LEN(col)-1)  
FROM (
	SELECT DISTINCT '['+LEFT(ResultData, CHARINDEX(':', ResultData)-1)+'], '
	FROM #TblResultXML
	FOR XML PATH('')
) AS X(col)

Changes the XML output produced by using FOR XML PATH to a VARCHAR type

Now let us extend our example by modifying the original XML file (Employees.xml) as below

<Employees>
    <Employee EmpId="Emp001" EmpName="Rajlakshmi" DOJ="02/03/2010" Age="4"/>   
    <Employee EmpId="Emp002" EmpName="Niladri" DOJ="10/08/2010" Age="37"/>
    <Employee EmpId="Emp003" EmpName="Arina" DOJ="01/01/2010" Age="26"/>
    <Employee EmpId="Emp004" EmpName="RNA Team" DOJ="31/12/2010" Age="4"/>
</Employees>

And after executing our query, we will receive the below output

So it's working!!!

References

a)Script to create dynamic PIVOT queries in SQL Server

b)Pivots with Dynamic Columns in SQL Server 2005

Conclusion

In this article we learnt to Import XML File and transform into Relational Model (for DYNAMIC Columns) using Powershell. Hope this will be helpful.You can find the zipped files attached herewith.Thanks for reading the article.