How to merge multiple queries into one single XML File

[Anonymous]

Hello,

I can usually Google my answers, but this one really has me stuck for the last three hours now. I could really use some input.

I am trying to merge multiple queries into one big XML file. Hook? Some queries return multiple rows for the same ID.

I think that this can be done using a tXMLMap - but I have failed to find how to do the INNER JOIN on each query.

How would I do this?

Let me give you an example:

entity1:

entity1Num | someOtherRow
1          | 'abc'
2          | 'def'
...

entity2:

entity1Num | field1 | field2 | field3 
1          | 'f1'   | 'f2'   | 'f3'
1          | 'f4'   | 'f5'   | 'f6'
1          | 'f7'   | 'f8'   | 'f9'
2          | 'f7'   | 'f8'   | 'f9'
...

entity3:

entity1Num | field1 | field2
1          | 'f1'   | 'f2'
...

Should yield:
<entities1>
	<entity1>
		<entity1Num>1</entity1Num>
		<entities2>
			<entity2>
				<field1>f1</field1>
				<field2>f2</field2>
				<field3>f3</field3>
			</entity2>
			<entity2>
				<field1>f4</field1>
				<field2>f5</field2>
				<field3>f6</field3>
			</entity2>
			<entity2>
				<field1>f7</field1>
				<field2>f8</field2>
				<field3>f9</field3>
			</entity2>
		</entities2>
		<someOtherRow>abc</someOtherRow>
	</entity1>
	...
</entities1>

I appreciate your answers. Thanks in advance!

Cheers!

[Anonymous]

Hi,

sure thing. Let me get back to you once I had the time to do this. Probably today or tomorrow!

Regards,
lib

[Anonymous]

Hi,

    Could you please also create the sample source files for all different entities as a csv file and attach them also along with other details? It will help our community members to quickly simulate the flow at their end.

Warm Regards,

Nikhil Thampi