DSXchange

Posted: **Tue May 08, 2018 10:29 am**

Hi everyone,
When running the following query we are seeing that any instances of '&' in BRANCH_NAME are being represented as '&amp' 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

We are able to get around this for now by using a replace before anything else on BRANCH_NAME for '&'.

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

I wanted to see if there might be a better approach to this?

Posted: **Tue May 08, 2018 11:42 am**

I'm not sure what the better way would be off the top of my head, but you are seeing that because of the XML involvement. Troublesome characters are being made 'safe':

Code: Select all

&   & 
"   "
'   &apos;
<   < 
>   >

Pretty sure there's a way to turn that substitution off... remember something about the "data element" and setting it to XML? Not sure if that's still valid, however.

Posted: **Tue May 08, 2018 6:30 pm**

It is very likely that the xml serialize of netezza is doing it. .....probably properly and by design. Any decent xml parser/serializerhas to do this because stray odd characters in xml content that also serve as markup will screw up a downstream parser. Let it be &amp.... whatever you deliver it to that knows xml will display it correctly.

Ernie

Posted: **Wed May 09, 2018 7:15 am**

Ahh that makes sense, unfortunately we are not using it to deal with any real XML or even be interpreted later as XML, but instead as a method of group_concat that is technically supported by IBM.

I was unable to get approval from management to install the UDF for group_concat out of fear it would eventually stop working and it is not covered under our IBM support agreement. This is the work around we followed.

http://dwgeek.com/netezza-group_concat- ... mple.html/

Posted: **Wed May 09, 2018 2:40 pm**

So... all good here?

Posted: **Tue May 15, 2018 6:58 am**

My apologies, yes, all good!

Posted: **Sat May 19, 2018 7:47 pm**

Or you could use Ereplace() function in DataStage to convert back to ampersand.

Posted: **Wed May 23, 2018 7:40 am**

Ray - That certainly would work, unfortunately this is a block of code that we are needing cognos to run directly. We did get it to work using replace() in the select statement.

DSXchange

Netezza SQL Extensions XML functions oddity

Netezza SQL Extensions XML functions oddity