Loading Multiple XML and using Sum

[paulm]

Hi,

I have a number of XML documents where I am trying to do a simple sum of records based on a certain date.  Below is the code, and I have added in a function to extract the date.  The records I need to see is the RecordCount in the MessageSent table..  There seems to be no link between the two when I do this.  Could anyone point out what I am doing wrong? 

Also attached a reduced example

Thanks
Paul

// Start of [DSO_MIM602_1358321939079.xml] LOAD statements

MessagesSent:

LOAD MessageTypeCode,

    RecordCount,

    %Key_ieXMLDocument_C7BCA21E04157E28    // Key to parent table: ieXMLDocument

FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesSent]);

MessagesReceived:

LOAD MessageTypeCode,

    RecordCount,

    %Key_ieXMLDocument_C7BCA21E04157E28    // Key to parent table: ieXMLDocument

FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesReceived]);

ieXMLDocument:

LOAD

          date(date#(left([MIM602_DailySummaryReconciliationCount/StartTime],10),'YYYY-MM-DD')-1,'DD/MM/YYYY')          as MM_DATE,

          [MIM602_DailySummaryReconciliationCount/StartTime] as StartTime,

    [MIM602_DailySummaryReconciliationCount/EndTime] as EndTime,

    [MessageHeader/MessageTypeCode],

    [MessageHeader/VersionNumber] as VersionNumber,

    [MessageHeader/MarketTimestamp] as MarketTimestamp,

    [MessageHeader/SenderID] as SenderID,

    [MessageHeader/RecipientID] as RecipientID,

    [MessageHeader/TxRefNbr] as TxRefNbr,

    %Key_ieXMLDocument_C7BCA21E04157E28    // Key for this table: ieXMLDocument

FROM C:\Users\Paul\Desktop\TIBCO\RAW\MPCC_Received\DSO_MIM602*.xml (XmlSimple, Table is [ieXMLDocument]);

// End of [DSO_MIM602_1358321939079.xml] LOAD statements

[michael123]

You need to loop on the xml-files. Something like this:

*********

FOR each FILE in filelist ('.\RAW\MPCC_Received\*.xml')

// Start of [DSO_MIM602_1358321939079.xml] LOAD statements
MessagesSent:
LOAD MessageTypeCode,
    RecordCount,
    FileName(FILE) as Key
//    %Key_ieXMLDocument_C7BCA21E04157E28    // Key to parent table: ieXMLDocument
FROM $(FILE) (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesSent]);

MessagesReceived:
LOAD MessageTypeCode,
    RecordCount,
    FileName(FILE) as Key
//    %Key_ieXMLDocument_C7BCA21E04157E28    // Key to parent table: ieXMLDocument
FROM $(FILE) (XmlSimple, Table is [ieXMLDocument/MIM602_DailySummaryReconciliationCount/MessagesReceived]);

ieXMLDocument:

LOAD 
date(date#(left([MIM602_DailySummaryReconciliationCount/StartTime],10),'YYYY-MM-DD')-1,'DD/MM/YYYY')          as MM_DATE,
[MIM602_DailySummaryReconciliationCount/StartTime] as StartTime,
    [MIM602_DailySummaryReconciliationCount/EndTime] as EndTime,
    [MessageHeader/MessageTypeCode],
    [MessageHeader/VersionNumber] as VersionNumber,
    [MessageHeader/MarketTimestamp] as MarketTimestamp,
    [MessageHeader/SenderID] as SenderID,
    [MessageHeader/RecipientID] as RecipientID,
    [MessageHeader/TxRefNbr] as TxRefNbr,
    FileName(FILE) as Key
//    %Key_ieXMLDocument_C7BCA21E04157E28    // Key for this table: ieXMLDocument
FROM $(FILE) (XmlSimple, Table is [ieXMLDocument]);
// End of [DSO_MIM602_1358321939079.xml] LOAD statements

NEXT FILE