Parsing the XML file in SSIS and populate on DB.

Question

Developers, I am working on SSIS and I want to create package to populate the XMLdata in the database from an XML source file and my source file sample data is below:

 

In the database table I have columns TimeStamp column, Meterdirection column, MeterName and I need the both values to populate in the columns of the table and there is one more table in available in the database with Meterdirection type with Delivered=1 and Received=2, So now how can I create the package in SSIS can anyone help me?

 

 

What I did: created a package inSSIS to Extract the XML file using the XML source and for the Load OLEDB Destination and in transformation I used the Derived column to get required column but in the destination table, MeterDataId and MeterName are not populating.

 

 

 

<?xml version="1.0"?>
<!-- MAS Release 7.0 ( build: Jan 22 2010 19:12:10 ) -->
 <MeterReadings Purpose="OnRequestRead" CollectionTime="2013-08-09
    19:57:11" Initiator="OnRequest" SourceIrn="30"
    SourceName="coll_0591_stuart" Source="Remote" Irn="21">

    <Meter MediaType="900 MHz" ObservesDaylightSavings="true"
    TimeZoneOffset="300" Timezone="(GMT-05:00) Eastern Time (US & Canada)"
    TimeZoneIndex="11" Location="" SdpIdent="" AccountName=""
    AccountIdent="" RemovalDate="" InstallDate="2013-05-01 20:28:09"
    Description="" MeterType="REX" SerialNumber="03310266" IsActive="true"
    MeterName="03310266" MeterIrn="21"/>

    <ConsumptionData>
    <ConsumptionSpec Multiplier="1"
    MeasurementPeriod="EndOfIntervalSnapshot" TouBucket="Total"
    Direction="Delivered" UOM="kWh"/>
    <Reading Value="73180" TimeStamp="2013-08-09 00:00:00"/>
    </ConsumptionData>

    <ConsumptionData>
    <LoadProfileSummary>
    <IntervalData>
    <IntervalData>
    <IntervalSpec Multiplier="0.1" Direction="Received" UOM="kWh"
    TimestampEnd="2013-08-09 00:00:00" TimestampStart="2013-08-02 00:15:00"
    Channel="2" Interval="15"/>
    <Reading TimeStamp="2013-08-02 00:15:00" RawReading="0"/>
    <Reading TimeStamp="2013-08-02 00:30:00" RawReading="0"/>
    <Reading TimeStamp="2013-08-02 00:45:00" RawReading="0"/>