When running the following query we are seeing that any instances of '&' in BRANCH_NAME are being represented as '&' in the output for NZ_CONCAT_STRING and anything that followed the '&' is omitted:
Code: Select all
select MARKET
, count(*) as NUM_OF_ROWS
, trim(trailing ', ' from replace(replace (XMLserialize(XMLagg(XMLElement('X',BRANCH_NAME ))), '<X>','' ),'</X>' ,', ' )) AS NZ_CONCAT_STRING
from
BRANCH_HIERARCHY
group by Market
order by 1
Code: Select all
select MARKET
, count(*) as NUM_OF_ROWS
, trim(trailing ', ' from replace(replace (XMLserialize(XMLagg(XMLElement('X',replace(BRANCH_NAME,'&','AND') ))), '<X>','' ),'</X>' ,', ' )) AS NZ_CONCAT_STRING
from
BRANCH_HIERARCHY
group by Market
order by 1