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 ""; --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 1 Carlos Santana 2 Prince 3 Jimi Hendrix
Read More
OpenXML - more than 8000 characters - SQL Server DBA