tHMap - how to group by node

[Anonymous]

Hi,

we have to create a mapping between flat data (returned by a database query) and a complex XML file with child loops at same level.

This kind of XML doesn't fit  in the common tXMLMap component, so the best solution would be using the tHMap component and the Talend Data Mapper features.

We are trying to create a very simple test with a simple XML, but I guess we are missing something.

This is our input data:

1             6617746               8314186
1             6617746               8314187

1             6617746               8314188

1             6617746               8314189

1             6617747               8314192

1             6617747               8314193

41           9240339               11687576

41           9240339               11687577

41           9240339               11687578

41           9240339               11687579

41           9240340               11687580

And the output that we are expeting is the following:

<?xml version="1.0" encoding="UTF-8"?>
<Container>
  <OUT>
    <ElencoNote>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617746</idContraente>
            <ElencoDreali>
              <idDreale>8314186</idDreale>
              <idDreale>8314187</idDreale>
              <idDreale>8314188</idDreale>
              <idDreale>8314189</idDreale>
            </ElencoDreali>
          </Contraente>
          <Contraente>
            <idContraente>6617747</idContraente>
            <ElencoDreali>
              <idDreale>8314192</idDreale>
              <idDreale>8314193</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240339</idContraente>
            <ElencoDreali>
              <idDreale>11687576</idDreale>
              <idDreale>11687577</idDreale>
              <idDreale>11687578</idDreale>
            </ElencoDreali>
          </Contraente>
          <Contraente>
            <idContraente>9240340</idContraente>
            <ElencoDreali>
              <idDreale>11687580</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
    </ElencoNote>
  </OUT>
</Container>

The problem is that the output we are getting creates 1 idNota for each idDreale, so I think that we should somehow group the data.

<?xml version="1.0" encoding="UTF-8"?>
<Container>
  <OUT>
    <ElencoNote>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617746</idContraente>
            <ElencoDreali>
              <idDreale>8314186</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617746</idContraente>
            <ElencoDreali>
              <idDreale>8314187</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617746</idContraente>
            <ElencoDreali>
              <idDreale>8314188</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617746</idContraente>
            <ElencoDreali>
              <idDreale>8314189</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617747</idContraente>
            <ElencoDreali>
              <idDreale>8314192</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>1</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>6617747</idContraente>
            <ElencoDreali>
              <idDreale>8314193</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240339</idContraente>
            <ElencoDreali>
              <idDreale>11687576</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240339</idContraente>
            <ElencoDreali>
              <idDreale>11687577</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240339</idContraente>
            <ElencoDreali>
              <idDreale>11687578</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240339</idContraente>
            <ElencoDreali>
              <idDreale>11687579</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
      <Nota>
        <idNota>41</idNota>
        <ElencoContraenti>
          <Contraente>
            <idContraente>9240340</idContraente>
            <ElencoDreali>
              <idDreale>11687580</idDreale>
            </ElencoDreali>
          </Contraente>
        </ElencoContraenti>
      </Nota>
    </ElencoNote>
  </OUT>
</Container>

Can you help us?

Thank you,

Vincenzo

[Anonymous]

Hello,

Do your output structure have invisible group and the representation is avro/xml?

Here exists a jira issue on talend bug tracker:https://jira.talendforge.org/browse/TDM-6355

Best regards

Sabrina

[Anonymous]

Hi,

basically we created the output structure only using an XML sample, so we don't have a schema to follow, we just know that we need to do a kind of group by.

[Anonymous · Accepted solution]

I think you can do this with an aggregate function, as explained here:

https://help.talend.com/reader/YTUJp4~lScETGDBqSSOt5g/pI7YtMQR5f9q1iK10yXXDw

The AgConcatFirstPresentValue should work, although you could also use AgMaximum (since the values for each node are identical within that node). You might also look at the looping cookbook, which has some examples of handling looping and non-looping map elements in the same mapper job. I believe you can also split the output schema into multiple sections that loop independently: this might also be helpful (I haven't done it, so YMMV).

https://help.talend.com/reader/YTUJp4~lScETGDBqSSOt5g/nyCFXAQTTDEuQ2hiCz28mg

Hope this helps. The Data Mapper is a great tool, but it's *not* intuitive.

David