SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Shredding XML with Powershell and SQL Server

24 May,2013 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer