topic Re: Converting MySQL row wise values to column values having no group by value in Talend Studio

Converting MySQL row wise values to column values having no group by value

Anonymous — Wed, 30 Jan 2019 05:20:27 GMT

Hi,

I have one XML file.I have stored that xml file response to a table as,

Id Key Value
------------------------------
1 EmpId 100
2 Name ABC
3 Age 23
4 EmpId 105
5 Name XYZ
6 Age 25

I want these values in the format as given below,

Id EmpId Name Age
----------------------------------------
1 100 ABC 23
2 105 XYZ 25

How can I convert it??

Thanks In Advance

Re: Converting MySQL row wise values to column values having no group by value

vapukov — Wed, 30 Jan 2019 06:10:08 GMT

Hi,

the most proper and easiest way - if you still have XML file (as followed from your message) it is parse XML with tExtractXMLFields

with MySQL it is not so easy - first of all because select without ORDER BY no warrant for you order of rows

if you sure - all tags stored with strong as id column (first 3 row - first client, next 3 row - next and etc)

personally I will add column emp_id and populate it with this logic, it will be:

Id          Key       Value        emp_id
----------------------------------------
1          EmpId     100           100
2          Name      ABC           100
3          Age         23          100
4          EmpId     105           105
5          Name      XYZ           105
6          Age         25          105

after this you can easy use SELECT with GROUP BY for prepare target structure

Re: Converting MySQL row wise values to column values having no group by value

Anonymous — Wed, 30 Jan 2019 06:37:19 GMT

But the XML response is like:

<item>
<key xsi:type="xsd:string">EmpId</key>
<value xsi:type="xsd:string">100</value>
</item>
<item>
<key xsi:type="xsd:string">Name</key>
<value xsi:type="xsd:string">ABC</value>
</item>

So while retrieving this XML file, It shows key & value as input columns.Then how can we extract values in the key as separate columns using tExtractXMLField??

Re: Converting MySQL row wise values to column values having no group by value

vapukov — Wed, 30 Jan 2019 10:48:39 GMT

I don't like work with partial information (because a lot of IF ...)

but as variant:

XML must have a structure (or it is broken form):

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <item>
        <key xsi:type="xsd:string">EmpId</key>
        <value xsi:type="xsd:string">100</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Name</key>
        <value xsi:type="xsd:string">ABC</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Age</key>
        <value xsi:type="xsd:string">23</value>
    </item>
    <item>
        <key xsi:type="xsd:string">EmpId</key>
        <value xsi:type="xsd:string">105</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Name</key>
        <value xsi:type="xsd:string">XYZ</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Age</key>
        <value xsi:type="xsd:string">25</value>
    </item>
    <item>
        <key xsi:type="xsd:string">EmpId</key>
        <value xsi:type="xsd:string">110</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Name</key>
        <value xsi:type="xsd:string">WHY</value>
    </item>
    <item>
        <key xsi:type="xsd:string">Age</key>
        <value xsi:type="xsd:string">30</value>
    </item>
</root>

this is easy to fix even if not, just add at the begin and end:

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


ALL ITEMS HERE

</root>

2. we are sure, each item contain same number of tag (allways)!!! this is important

in this case it is easy:

we use separate flows for EmpId, name, Age (with XPath filter)

and then add in tMap row number (just a sequence, but separate sequence for each filtered row)

and finally join flows by row number, with result as:

.-----+----+---.
|  tLogRow_1   |
|=----+----+--=|
|EmpId|Name|Age|
|=----+----+--=|
|100  |ABC |23 |
|105  |XYZ |25 |
|110  |WHY |30 |
'-----+----+---'