XML with multiple header and detail tags

[Anonymous]

Hi Everyone,

I have an input XML file with structure like below:

<xml>
    <Fields>
        <MessageId>1</MessageId>
        <Timestamp>2018-10-18</Timestamp>
    </Fields>
    <InvoiceHeader>
        <Fields>
            <Company>Test</Company>
            <InvoiceDate>2018-10-18</InvoiceDate>
            <InvoiceNumber>1346</InvoiceNumber>
        </Fields>
        <InvoiceItem>
           <Fields>
                <InvoiceNumber>1346</InvoiceNumber>
                <ItemId>23456</ItemId>
                <PurchasePrice>12.34</PurchasePrice>
           </Fields>
           <Fields>
                <InvoiceNumber>1346</InvoiceNumber>
                <ItemId>45789</ItemId>
                <PurchasePrice>5.34</PurchasePrice>
           </Fields>
        </InvoiceItem>
    </InvoiceHeader>
    <InvoiceHeader>
        <Fields>
            <Company>Test2</Company>
            <InvoiceDate>2018-10-18</InvoiceDate>
            <InvoiceNumber>2487</InvoiceNumber>
        </Fields>
        <InvoiceItem>
           <Fields>
                <InvoiceNumber>2487</InvoiceNumber>
                <ItemId>234</ItemId>
                <PurchasePrice>152.34</PurchasePrice>
           </Fields>
           <Fields>
                <InvoiceNumber>2487</InvoiceNumber>
                <ItemId>4579</ItemId>
                <PurchasePrice>65.34</PurchasePrice>
           </Fields>
        </InvoiceItem>
    </InvoiceHeader>
</xml>

I need to get Positional TXT file as an output with structure like below:

InvoiceHeader1  Company InvoiceDate InvoiceNumber
InvoiceItem1 InvoiceNumber ItemId PurchasePrice
InvoiceItem2 InvoiceNumber ItemId PurchasePrice
InvoiceHeader2 Company InvoiceDate InvoiceNumber
InvoiceItem1 InvoiceNumber ItemId PurchasePrice
InvoiceItem2 InvoiceNumber ItemId PurchasePrice

Now, I was able to pass all data from Input to Output but in format:

InvoiceHeader1  Company InvoiceDate InvoiceNumber
InvoiceHeader2 Company InvoiceDate InvoiceNumber
InvoiceItem1 InvoiceNumber ItemId PurchasePrice
InvoiceItem2 InvoiceNumber ItemId PurchasePrice
InvoiceItem1 InvoiceNumber ItemId PurchasePrice
InvoiceItem2 InvoiceNumber ItemId PurchasePrice

Does anyone have any advice on how I can get InvoiceHeader and InvoiceItems to appear in desired order?

Is there any component I could use for this, or I should maybe restructure XML file to some intermediate format and then parse that one into the TXT file?

Thanks!

Pavle

[Anonymous · Accepted solution]

This is resolved. I had to restructure tFileOutputPositional file (added column to sort by) and then use tSortRow component to get the desired output once I got all rows within the tFileOutputPositional file.

Best Regards,

Pavle