Shredding XML with Powershell and SQL Server

How can I shred XML using Powershell and SQL Server? I have an XML source which requires to be shredded and placed into Entity-attribute-model (EAV) table.

 The query takes an XML document , inserts into a table , builds an XML string and shreds the xml into a table – applying the Entity-Attribute-model .

The query takes an XML document , inserts into a table , builds an XML string and shreds the xml into a table – applying the Entity-Attribute-model .

This code is tested on SQL Server 2008 R2

DECLARE @XML_str VARCHAR(MAX);
DECLARE @Guitarists XML;
--Insert data in order , into a table variable
DECLARE @XML TABLE (XML_source VARCHAR(2000), OrderRow INT IDENTITY(1,1));
--insert the XML into the table, line by line
INSERT INTO @XML(XML_source)
SELECT "
           
	              1
		          Carlos Santana
		     
		    
	              2
		          Prince
		        
		   
	              3
		          Jimi Hendrix
		        
		

           	
";

--build the XML as a string 
SELECT @XML_str=COALESCE(@XML_str,'') + XML_source 
FROM @XML 
WHERE XML_source IS NOT NULL 
ORDER BY OrderRow;
--save the string into a variable 
SELECT @Guitarists = @XML_str;
-- Create a Pivot of the  XML 
SELECT MAX (CASE WHEN attribute='Guitarist_ID' THEN VALUE ELSE '' END ) AS [Guitarist_ID],
MAX (CASE WHEN attribute='Guitarist_Name' THEN VALUE ELSE '' END ) AS [Guitarist_Name]
FROM 
( 
SELECT --XML shred applying the Entity-attribute-value model
[property].value('(./text())[1]', 'Varchar(20)') AS [Value],
[property].value('@Name', 'Varchar(20)') AS [Attribute],
DENSE_RANK() OVER (ORDER BY [object]) AS Guitarist
FROM @Guitarists.nodes('Musicians/Guitarist') AS b ([object])
CROSS APPLY b.object.nodes('./Property') AS c (property)
) Guitarists
GROUP BY Guitarist;

--outputed results
Guitarist_ID	Guitarist_Name
1	Carlos Santana
2	Prince
3	Jimi Hendrix

Read More

OpenXML - more than 8000 characters - SQL Server DBA

Powershell for SQL SMO

Export-CSV Powershell