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 a delimiter on the results ? like
12 3, 4 56
CodePudding user response:
You can REPLACE()
whitespaces with a comma such as
SELECT REPLACE(
ExtractValue(attributes, '/Attributes/Map/entry/value/List/String'),' ',','
) AS Strings
FROM t
CodePudding user response:
It seems that you are using MySQL.
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 |