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

[Anonymous]

Hello everybody,

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

The XML- structure is as seen below:

<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>

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.

[akumar2301 · Accepted solution]

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

[Anonymous]

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 🙂

[Anonymous]

@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 🙂

[Anonymous]

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.

[akumar2301]

Hello @CB_Alias ,

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

[Anonymous]

Thank you!