Originally posted by: paul.brett

It is entirely possible to modify the Typetree imported by the database Interface Designer, to add additional structure that will aid in the mapping process.

In this particular scenario, the data from the GB was a JOIN of 2 tables.  The output structure was such that a lot of mapping work was required with EXTRACT() functions, to get the desired result.

Input:

Table 1:

1|TESCO STORES LTD
2|SAINSBURYS RETAIL LTD

Table 2:

1|1|'C'|2042.17
1|2|'D'|8643.21
1|3|'C'|853.79
2|1|'D'|826.48
2|2|'C'|4192.41
2|3|'C'|791.62

JOINed data:

1|TESCO STORES LTD|1|C|2042.17
1|TESCO STORES LTD|2|D|8643.21
1|TESCO STORES LTD|3|C|853.79
1|TESCO STORES LTD|4|C|1078.91
2|SAINSBURYS RETAIL LTD|1|D|826.48
2|SAINSBURYS RETAIL LTD|2|C|4192.41
2|SAINSBURYS RETAIL LTD|3|C|791.62

Output:

<?xml version="1.0" encoding="UTF-8"?>
<root>
        <Payment>
                <PaymentID>1</PaymentID>
                <Payee>TESCO STORES LTD</Payee>
                <Transaction>
                        <TransID>1</TransID>
                        <TransType>C</TransType>
                        <Amount>2042.17</Amount>
                </Transaction>
                <Transaction>
                        <TransID>2</TransID>
                        <TransType>D</TransType>
                        <Amount>8643.21</Amount>
                </Transaction>
                <Transaction>
                        <TransID>3</TransID>
                        <TransType>C</TransType>
                        <Amount>853.79</Amount>
                </Transaction>
                <Transaction>
                        <TransID>4</TransID>
                        <TransType>C</TransType>
                        <Amount>1078.91</Amount>
                </Transaction>
        </Payment>
        <Payment>
                <PaymentID>2</PaymentID>
                <Payee>SAINSBURYS RETAIL LTD</Payee>
                <Transaction>
                        <TransID>1</TransID>
                        <TransType>D</TransType>
                        <Amount>826.48</Amount>
                </Transaction>
                <Transaction>
                        <TransID>2</TransID>
                        <TransType>C</TransType>
                        <Amount>4192.41</Amount>
                </Transaction>
                <Transaction>
                        <TransID>3</TransID>
                        <TransType>C</TransType>
                        <Amount>791.62</Amount>
                </Transaction>
        </Payment>
</root>

Check out my solution in Test2, with an additional wrapping group, with Control-Break logic, to match the output XML structure.

Thank-you.

Paul.

Follow me on Twitter