extractValue SQL retuning multiple values in the XML in the same column followed by space

Question

I'm using MySQL database and below is the sample XML in a attributes column of the table mytable. I used extractValue to pull the values from XML nodes and the values are returned in a single row and are separated by a space. I want the results to be in a different rows of the same column.

The column attributes in mytable holds the below XML that I need to query upon.

        <Attributes>
            <Map>
               <entry key="ABC">
                  <value>
                   <List>
                      <String>12 3</String>          
                      <String>4 56</String>    
                   </List>
                 </value>
               </entry>
            </Map>
        </Attributes>

I used the extractValue function to pull the values from XML nodes

       SELECT itemno, extractValue(attributes, '/Attributes/Map/entry[1]/value/List/String') 
               as Value FROM mytable 
        

The output with the above SQL is returning:

itemno    Value
------    -----
1         12 3  45 6

and I want it as below

itemno    Value
------    -----
1         12 3
1         45 6

How can I achieve this?

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
CREATE TABLE tbl (ID INT, attributes TEXT);
INSERT INTO tbl VALUES
(1, '<Attributes>
    <Map>
        <entry key="ABC">
            <value>
                <List>
                    <String>12 3</String>
                    <String>4 56</String>
                </List>
            </value>
        </entry>
    </Map>
</Attributes>');
-- DDL and sample data population, end

SELECT ID, ExtractValue(attributes,
     '/Attributes/Map/entry[1]/value/List/String[1]/text()') as tokens
FROM tbl
UNION ALL
SELECT ID, ExtractValue(attributes,
     '/Attributes/Map/entry[1]/value/List/String[2]/text()') as tokens
FROM tbl;