ExtractValue SQL returning multiple values from XML nodes

Question

I have the below XML

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

The below SQL

SELECT COALESCE(
                 ExtractValue(
                               attributes, 
                               '/Attributes/Map/entry[1]/value/List/String'), 
                'N/A'
                )

  FROM mytable

is returning the below

12 3 4 56

How can I get results in separate rows

12 3

4 56

Answer 1

It seems that you are using MySQL.

dbfiddle

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>123</String>
                    <String>456</String>
                </List>
            </value>
        </entry>
    </Map>
</Attributes>');
-- DDL and sample data population, end

SELECT ID, REPLACE(COALESCE(ExtractValue(attributes,
     '/Attributes/Map/entry[1]/value/List/String'), 'N/A'), ' ', ', ') as tokens
FROM tbl;

Output

ID	tokens
1	123, 456