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

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?

1 Answer

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;