Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

normalizing XML data via transformation Expand / Collapse
Author
Message
Posted Monday, October 08, 2012 7:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 5:29 AM
Points: 28, Visits: 355
I'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:
...
<Appointments>
<Appointment id = "1">
<person>
<Name>John Doe</Name
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "2">
<person>
<Name>Jane Doe</Name
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "3">
<person>
<Name>george Doe</Name
</person>
<location>
<name>Satellite</name>
<address>123 Main St.</address>
<State>NY</State>
</location>
</Appointment>
</Appointments>
...

How do I get that to be something more like
...
<Appointments>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
<location>
<name>Satellite</name>
<address>123 Main St.</address>
<State>NY</State>
</location>
<Appointment id = "3">
<person>
<Name>george Doe</Name
</person>
<location name = "Satellite"/>
</Appointment>
<Appointment id = "2">
<person>
<Name>Jane Doe</Name
</person>
<location name ="Main"/>
</Appointment>
<Appointment id = "1">
<person>
<Name>John Doe</Name
</person>
<location name ="Main"/>
</Appointment>
</Appointments>
...

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,
Sean
Post #1370122
Posted Tuesday, October 09, 2012 1:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:36 AM
Points: 2,423, Visits: 1,565
Personally 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:

DECLARE @xml XML
SET @xml = '<Appointments>
<Appointment id = "1">
<person>
<Name>John Doe</Name>
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "2">
<person>
<Name>Jane Doe</Name>
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "3">
<person>
<Name>george Doe</Name>
</person>
<location>
<name>Satellite</name>
<address>123 Main St.</address>
<State>NY</State>
</location>
</Appointment>
</Appointments>'

SELECT @xml.query('for $x in /Appointments return
<Appointments>
{
for $y in distinct-values($x/Appointment/location/name/text())
return
($x/Appointment/location[name = $y])[1]

}
{
for $y in $x/Appointment
return
<Appointment id="{$y/@id}">
{$y/person}
<location name="{$y/location/name/text()}"/>
</Appointment>
}
</Appointments>
')

You can see the main guts of the code is quite small and remodels the xml as you require.

http://msdn.microsoft.com/en-us/library/ms189075.aspx
Post #1370195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse