SQLServerCentral.com / Programming / XML / normalizing XML data via transformation / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:48:57 GMT20RE: normalizing XML data via transformationhttp://www.sqlservercentral.com/Forums/Topic1370122-21-1.aspxPersonally I would use XQuery to do the transformation. I save xslt for the hardest of transformations, but to be honest, I have been able to do most things in XQuery. If the data is already stored in your sql tables, this makes it a bit easier as you can use tsql and not have to worry about creating a clr or another app/ssis to read the data out, do the transformation and save it back.for example, I quickly created the following based on your example you documented to do the transformation using xquery:[code="sql"]DECLARE @xml XMLSET @xml = '&lt;Appointments&gt;&lt;Appointment id = "1"&gt;&lt;person&gt;&lt;Name&gt;John Doe&lt;/Name&gt;&lt;/person&gt;&lt;location&gt;&lt;name&gt;Main&lt;/name&gt;&lt;address&gt;456 Oak St.&lt;/address&gt;&lt;State&gt;NY&lt;/State&gt;&lt;/location&gt;&lt;/Appointment&gt;&lt;Appointment id = "2"&gt;&lt;person&gt;&lt;Name&gt;Jane Doe&lt;/Name&gt;&lt;/person&gt;&lt;location&gt;&lt;name&gt;Main&lt;/name&gt;&lt;address&gt;456 Oak St.&lt;/address&gt;&lt;State&gt;NY&lt;/State&gt;&lt;/location&gt;&lt;/Appointment&gt;&lt;Appointment id = "3"&gt;&lt;person&gt;&lt;Name&gt;george Doe&lt;/Name&gt;&lt;/person&gt;&lt;location&gt;&lt;name&gt;Satellite&lt;/name&gt;&lt;address&gt;123 Main St.&lt;/address&gt;&lt;State&gt;NY&lt;/State&gt;&lt;/location&gt;&lt;/Appointment&gt;&lt;/Appointments&gt;'SELECT @xml.query('for $x in /Appointments return&lt;Appointments&gt; { for $y in distinct-values($x/Appointment/location/name/text()) return ($x/Appointment/location[name = $y])[1] } { for $y in $x/Appointment return &lt;Appointment id="{$y/@id}"&gt; {$y/person} &lt;location name="{$y/location/name/text()}"/&gt; &lt;/Appointment&gt; }&lt;/Appointments&gt;')[/code]You can see the main guts of the code is quite small and remodels the xml as you require.[url]http://msdn.microsoft.com/en-us/library/ms189075.aspx[/url]Tue, 09 Oct 2012 01:29:18 GMTArthur Olcotnormalizing XML data via transformationhttp://www.sqlservercentral.com/Forums/Topic1370122-21-1.aspxI'm a newbie with XML, but i was wondering if somebody could get me started down the path of how to normalize out some XML data. I *think* if I can do this via XSLT, but not sure of what to research. Basically I have nodes that across the document have repeating groups, and I'm curious if I can normalize them into a common node. I'm hoping that I can use this as part of an SSIS package. I would have the first XSD, and the second XSD, what I don't have is the "transalation" peice.For example:...&lt;Appointments&gt; &lt;Appointment id = "1"&gt; &lt;person&gt; &lt;Name&gt;John Doe&lt;/Name &lt;/person&gt; &lt;location&gt; &lt;name&gt;Main&lt;/name&gt; &lt;address&gt;456 Oak St.&lt;/address&gt; &lt;State&gt;NY&lt;/State&gt; &lt;/location&gt;&lt;/Appointment&gt; &lt;Appointment id = "2"&gt; &lt;person&gt; &lt;Name&gt;Jane Doe&lt;/Name &lt;/person&gt; &lt;location&gt; &lt;name&gt;Main&lt;/name&gt; &lt;address&gt;456 Oak St.&lt;/address&gt; &lt;State&gt;NY&lt;/State&gt; &lt;/location&gt;&lt;/Appointment&gt; &lt;Appointment id = "3"&gt; &lt;person&gt; &lt;Name&gt;george Doe&lt;/Name &lt;/person&gt; &lt;location&gt; &lt;name&gt;Satellite&lt;/name&gt; &lt;address&gt;123 Main St.&lt;/address&gt; &lt;State&gt;NY&lt;/State&gt; &lt;/location&gt;&lt;/Appointment&gt;&lt;/Appointments&gt;...How do I get that to be something more like...&lt;Appointments&gt; &lt;location&gt; &lt;name&gt;Main&lt;/name&gt; &lt;address&gt;456 Oak St.&lt;/address&gt; &lt;State&gt;NY&lt;/State&gt; &lt;/location&gt; &lt;location&gt; &lt;name&gt;Satellite&lt;/name&gt; &lt;address&gt;123 Main St.&lt;/address&gt; &lt;State&gt;NY&lt;/State&gt; &lt;/location&gt; &lt;Appointment id = "3"&gt; &lt;person&gt; &lt;Name&gt;george Doe&lt;/Name &lt;/person&gt; &lt;location name = "Satellite"/&gt; &lt;/Appointment&gt; &lt;Appointment id = "2"&gt; &lt;person&gt; &lt;Name&gt;Jane Doe&lt;/Name &lt;/person&gt; &lt;location name ="Main"/&gt; &lt;/Appointment&gt; &lt;Appointment id = "1"&gt; &lt;person&gt; &lt;Name&gt;John Doe&lt;/Name &lt;/person&gt; &lt;location name ="Main"/&gt; &lt;/Appointment&gt;&lt;/Appointments&gt;...Or something like that. I'm not that concerned with element v attribute, i'm more interested in how would I do the "normalization" peice. Right now being a DBA, i'm more inclined to load it to a staging database and do the "translation" via T-SQL, but I'm going to attempt to broaden my mind (scary).Thanks in advance,SeanMon, 08 Oct 2012 19:00:46 GMTsean hawkes