Netezza SQL Extensions XML functions oddity

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Netezza SQL Extensions XML functions oddity

Post by jackson.eyton »

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?
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

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
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

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/
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So... all good here?
-craig

"You can never have too many knives" -- Logan Nine Fingers
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

My apologies, yes, all good!
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Or you could use Ereplace() function in DataStage to convert back to ampersand.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

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.
-Me
Post Reply