normalizing XML data via transformation

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply