Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Idea Lecture de fichier XML

More
11 Dec 2019 15:10 #1 by ENDY
Lecture de fichier XML was created by ENDY
Bonjour,

J'ai eu un soucis de lecture de fichier XML,

J'ai une structure de XML comme suite:
<siret>012345678901234</siret>
<postalCompanyName>
<line>RAISON SOCIALE LIGNE1</line>
<line>RAISON SOCIALE LIGNE2</line>
</postalCompanyName>

et je voudrais les charger dans une table structuré comme suite

SIRET | RAI_SOC1 | RAI_SOC2
avez vous une idée comment je puisse faire?
More
12 Dec 2019 14:45 - 12 Dec 2019 14:46 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Lecture de fichier XML
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 :)
Attachments:
    Last edit: 12 Dec 2019 14:46 by Thomas BLETON.