topic Re: How to merge multiple queries into one single XML File in Talend Studio

How to merge multiple queries into one single XML File

Anonymous — Thu, 08 Nov 2018 13:49:56 GMT

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!

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 07:15:37 GMT

Hi,

Could you please change the perspective of Talend to Mapping mode and try to create structure and mapping using Talend Data Mapper. That will be more easier way to maintain XML feeds since its storing structures and mappings also.

Warm Regards,

Nikhil Thampi

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 09:13:01 GMT

Hi,

I don't seem to have this option in my Open Talend Studio 7.0.1

Where would I find this?

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 09:21:19 GMT

Hi,

Unfortunately it is not available in Open Studio version. So the current issue has to be dealt with txmlmap itself.

Warm Regards,

Nikhil Thampi

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 09:44:18 GMT

Hi,

so does this man that the scenario, as I have explained it is actually solvable using tXMLMap?

If so, could you give me some hints how to do this? Unfortunately I have already spent way too much time on trying to figure this out and I think I am now thinking in circles. Any advice will be greatly appreciated.

Thanks & regards,

lib

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 11:36:21 GMT

Hi,

I have done some quick job flow creation. I was able to consolidate the data for each driving table record. Since your output do not have any reference to enity3, I have skipped it.

The representation of txmlmap is as below.

Since I have to jump to some meetings, I could not investigate it further. Hope it has helped you. If you need to consolidate/aggregate further, I would suggest to use an additional txmlmap (or a tjavarow to remove or add the details programmatic manner) so that tracking of data will be easy.

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: How to merge multiple queries into one single XML File

Anonymous — Fri, 09 Nov 2018 13:41:19 GMT

Hi,

I thank you SO MUCH for your help. I really appreciate it.

It worked too! I now have the first part of my XML, hooray.

My requirements are that I should be able to add entity1,entity2,...,entityN.

<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>
<!-- NEW -->
		<entity3>
			<field1>f1</field1>
			<field2>f2</field2>
                        <!-- NEW START -->
                        <entities4>
                            <entity4>
                               <fieldA>fa</fieldA>
                               <fieldB>fb</fieldB>
                               <fieldC>fc</fieldC>
                            </entity4>
                            <entity4>
                               <fieldA>fz</fieldA>
                               <fieldB>fy</fieldB>
                               <fieldC>fx</fieldC>
                            </entity4>
                        </entities4>
                        <!-- NEW END -->
		</entity3>
<!-- END NEW -->
		<someOtherRow>abc</someOtherRow>
	</entity1>
	...
</entities1>

Unfortunately I have tried to do this, but I have yet failed again.

Would you mind to help me out one more time, please?

Thanks & kindes regards,

lib

Re: How to merge multiple queries into one single XML File

Anonymous — Sun, 11 Nov 2018 07:04:36 GMT

Hi,

Could you please share the sample incoming records for entity 3 and entity 4 and how they are related to entity 1? You can create the data in tabular format like you had given in your first post.

Warm Regards,

Nikhil Thampi

Re: How to merge multiple queries into one single XML File

Anonymous — Mon, 12 Nov 2018 08:55:38 GMT

Hi,

again thank you so much for your follow up!

My record would look like this. The problem I am currently having is that my firm is unsure on how the data is actually really going to look like.

entity1:
entity1Num | field1 | ...
1          | 'f1'   | ...

entity3:
<!-- entity3 only ever belongs to entity1 once, although this might be subject to change -->
entity1Num | field1 | field2
1          | 'f1'   | 'f2'
2          | 'a1'   | 'a2'

entity4 (e3's subentity)
entity1Num | field1 | field2
1          | 'f1'   | 'f2'
1          | 'f3'   | 'f4'
2          | 'f5'   | 'f6'
2          | 'f7'   | 'f8' 
<!-- it might happen that one e1 has multiple e3s -->

<entity1>
    <entities2>
       <!-- ... -->
    </entities2>
   <entities3>
      <entity3>
         <!-- ... -->
         <entities4>
            <entity4>
               <!-- ... -->
            </entity4>
         </entities4>
      </entities3>
   </entities3>
</entity1>

Re: How to merge multiple queries into one single XML File

Anonymous — Mon, 12 Nov 2018 13:46:25 GMT

Hi,

Could you first create an XSD file where you can define your schema correctly and then create a sample XML based on your current result set and verify whether the schema is matching with the results you are planning to create?

You can use the below link to validate the XSD with XML.

https://www.freeformatter.com/xml-validator-xsd.html

I am worried whether the loops at entity 3 and entity 4 will create problem as the loops are at two different levels for same branch. Could you please check whether it can be simplified further?

Warm Regards,

Nikhil Thampi

Re: How to merge multiple queries into one single XML File

Anonymous — Tue, 13 Nov 2018 09:29:42 GMT

Hi,

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

Regards,
lib

Re: How to merge multiple queries into one single XML File

Anonymous — Tue, 13 Nov 2018 09:33:13 GMT

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