Oui c'est possible, voici un exemple de démarche pour y parvenir.
I reply in english so that the example can be useful for more people.
Source XML structure
Your example is not a fully valid Xml structure (no root node), so I assume it was an abstract from a bigger file.
Here is the source file I'm going to use for my example :
<data>
<company>
<siret>012345678901234</siret>
<postalCompanyName>
<line>RAISON SOCIALE LIGNE1</line>
<line>RAISON SOCIALE LIGNE2</line>
</postalCompanyName>
</company>
<company>
<siret>678901234012345</siret>
<postalCompanyName>
<line>NAME LINE 1</line>
<line>NAME LINE 2</line>
</postalCompanyName>
</company>
<company>
<siret>893401230123467</siret>
<postalCompanyName>
<line>OTHER COMPANY 1</line>
<line>OTHER COMPANY 2</line>
<line>OTHER COMPANY 3</line>
</postalCompanyName>
</company>
</data>
Note: in this example I added a 3rd line element in a company node, but the target table only has 2 columns. This example simply ignores the extra lines.
After reversing the metadata :
We added a Property Field "nodeLocalPosition" to the Line element, so that we can transpose this item into columns.
Target table
I'm using a Postgresql db :
Create table public."F505_TARGET"
(
"SIRET" VARCHAR(50) NOT NULL,
"SOCNAME1" VARCHAR(100) NOT NULL,
"SOCNAME2" VARCHAR(100) NOT NULL
)
After reversing the metadata :
Creating the mapping
- The source is the XML metadata
- I load the XML in a first Stage with a "In Memory" database
- Next, I split the first stage into 2 stages to transpose the line elements into separate entities.
- Each stage is loaded with the help of a Target Filter with this expression: stage.line_number = 1
- stage_line1 and stage_line2 are joined with the SIRET column
- Until now, we have only manipulated data in memory (no physical table created in the target database)
- finally, the table F505_TARGET is loaded with the template "INTEGRATION Rdbms to Rdbms (no load)" (this one because I don't need any incremental mode, just inserts)
Result after execution
I hope you can adapt this example to your needs
