topic Select multiple XML Elements and write them in one Column as several rows in Talend Studio

Select multiple XML Elements and write them in one Column as several rows

Anonymous — Fri, 05 Apr 2019 12:52:32 GMT

Hello everybody,

I've got a problem inserting XML - data with many parameters into a MSSQL database.

The XML- structure is as seen below:

</Header>

<Parameter_A>

<Value>Value A</Value>

<Parameter_A>

<Parameter_B>

<Value>Value B</Value>

<Parameter_B>

<Parameter_C>

<Value>Value C</Value>

<description>Workplace</description>

<Parameter_C>

[...]

</VParameters>

</Document>

I want to select the description and value of each parameter and insert it into sql rows named "description" and "parameter". It should look like this:

Location | ID | Type | description | Value

1234 |12345678 | A | Name | Value A

1234 |12345678 | A | Time | Value B

1234 |12345678 | A | Workplace | Value C

I load the XML with a tFileInputXML and tried to connect the parameters using tXMLMap. But that doesn't work.

Can someone explain how to solve this problem?

Thanks for your help.

Re: Select multiple XML Elements and write them in one Column as several rows

akumar2301 — Fri, 05 Apr 2019 13:35:23 GMT

try like this

Output

[statistics] connected
.--------+--------+----+----+-----------+-------.
| tLogRow_1 |
|=-------+--------+----+----+-----------+------=|
|Location|ID |Type|Unit|description|Value |
|=-------+--------+----+----+-----------+------=|
|1234 |12345678|A |a |Name |Value A|
|1234 |12345678|A |s |Time |Value B|
|1234 |12345678|A |c |Workplace |Value C|
'--------+--------+----+----+-----------+-------'
[statistics] disconnected

Re: Select multiple XML Elements and write them in one Column as several rows

Anonymous — Fri, 05 Apr 2019 13:37:36 GMT

Hi,

The current XML is wrong as most of the parameters are not having closing tags. I hope its an error while creating the XML manually. Coming to the parameter list, I believe the list of parameters is predetermined and it will not get added during run time.

For example, is it a good assumption to think that below XML is correct one?

<Document>

     <Header>

            <Location>1234</Location>

     </Header>

     <VParameters>

          <ID>12345678</ID>

          <Type>A</Type>

          <Parameter_A>

              <Value>Value A</Value>

              <description>Name</description>

              <Unit></Unit>

          </Parameter_A>

 

          <Parameter_B>

              <Value>Value B</Value>

              <description>Time</description>

              <Unit>s</Unit>

          </Parameter_B>

 

          <Parameter_C>

              <Value>Value C</Value>

              <description>Workplace</description>

              <Unit></Unit>

          </Parameter_C>

     </VParameters>

</Document>

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Select multiple XML Elements and write them in one Column as several rows

Anonymous — Fri, 05 Apr 2019 13:39:06 GMT

@uganesh

Good one buddy 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Select multiple XML Elements and write them in one Column as several rows

Anonymous — Fri, 05 Apr 2019 14:53:13 GMT

Thanks for answering my question. The XML you see is just an example for how the real file looks like. The real data won't change during runtime.

Re: Select multiple XML Elements and write them in one Column as several rows

akumar2301 — Fri, 05 Apr 2019 15:18:24 GMT

Hello @CB_Alias ,

If all OK , Please mark the post as resolved as it helps others in Community.

Re: Select multiple XML Elements and write them in one Column as several rows

Anonymous — Fri, 05 Apr 2019 15:34:41 GMT

Thank you!