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

[Anonymous]

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

[vapukov]

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

[Anonymous]

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

[vapukov]

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

but as variant:

1.

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