CODEWITHSUNDEEP
sqlsql-serverxmlmerge
Yairt
Yairt

Reputation: 167

SQL Server : how to merge XML into several tables

My schema has 3 levels and goes like this:

Triggers

TRIGGER_ID (PK), TARGET_GROUP_SERIAL, EVENT_ID, START_DATE, END_DATE, MEASURE_FIELD, IS_ACTIVE, PRIORITY

TriggerActions

TRIGGER_ID, TRIGGER_DETAIL_ID (PK + Identity Insert), ACTION_ID, ISOLATION_VOLUME

ActionChannels

TRIGGER_DETAIL_ID (PK), EXECUTION_METHOD_ID

Now, my input is an XML file which and I need to merge it into the DB schema, the XML can look like below,

Notice that if the XML does not contain a TriggerID in the RealTimeTrigger it means a new trigger and when the ActionDetailId = 0 it means a new action

Any suggestions?

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfRealTimeTrigger xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <RealTimeTrigger>
    <TargetGroupSerial>3</TargetGroupSerial>
    <ClientTriggerId>1</ClientTriggerId>
    <StartDate>2014-08-06T00:00:00</StartDate>
    <EndDate>2014-09-03T00:00:00</EndDate>
    <MeasureField>FIELD4</MeasureField>
    <IsActive>true</IsActive>
    <Priority>999</Priority>
    <ControlGroupPercentage>20</ControlGroupPercentage>
    <Actions>
      <RealTimeAction>
        <ActionDetailId>0</ActionDetailId>
        <Id>12</Id>
        <Channels>
            <Channel>
                <ChannelId>100</ChannelId>
                <ChannelId>201</ChannelId>
            </Channel>
        </Channels>
        <ActionPrecentage>40</ActionPrecentage>
      </RealTimeAction>
      <RealTimeAction>
        <ActionDetailId>0</ActionDetailId>
        <Id>13</Id>
        <Channels>
            <Channel>
                <ChannelId>100</ChannelId>
                <ChannelId>201</ChannelId>
            </Channel>
        </Channels>
        <ActionPrecentage>40</ActionPrecentage>
      </RealTimeAction>
    </Actions>
  </RealTimeTrigger>
</ArrayOfRealTimeTrigger>'

Upvotes: 0

Views: 90

Answers (1)

Ross Bradbury
Ross Bradbury

Reputation: 605

If there aren't id values I the XML linking the child elements together (if the are linked just by nesting in XML) then I would use a select with ROWNUMBER to assign temporary ids to the rows that were in the XML. Then I think you could do a MERGE into the triggers table with an into a table variable. In that table variable you can have a column for the original/ROWNUMBER based id and a column for the id as it was inserted for the new records. Then you have enough data to join things together and repeat for the next two levels.

Let me know if you need more details, or maybe this is enough for someone else to expand upon.

Upvotes: 1

Related Questions