MVSFORUMS.com

[bauer]

Dear *,

I have a table something like this:

[NASCAR9]

[kolusu]

bauer,

I think CONTENT is reserved variable that you cannot use a column name. I assumed it as CON_TEXT and here is a SQL ( quite similar to Nascar9's) that will give you the results

[bauer]

Dear NASCAR9,
dear kolusu,

yes, this solution with the XMLAGG function works perfect. Much better than the recursive version.

Have a nice day,
thank you,
bauer

[NASCAR9]

Kolusu,
I see where I omitted TEXTNUMBER from my results.

I used your model and added an Order BY(just to show another option) and removed REPLACE(REPLACE
I tested with know data in our system and the result were accurate. Can you explain why the "ABC" Replace logic? Always trying to learn.

[bauer]

Dear *,

according the replace(replace ( .....:

My understanding:

XMLELEMENT(NAME "ABC",A.CON_TEXT) creates something like this:

<ABC>.....</ABC>

This XML brackets of course must be removed after the aggregate function XMLAGG and serializing with XMLSERIALIZE.

I added an additional replace (<ABC/>,'') just in case A.CON_TEXT is empty.

The solution with XMLTEXT instead of XMLELEMENT and avoiding the replace is very interesting. Background: The SQL REPLACE works only for CHARACTER data, not for CLOB data. Just in case the concatenated text is greater than 32K the solution without REPLACE might avoid overflow problems.

I will test this solution the next days and give feedback. (I'm without mainframe access at the moment).


[11.06.2018]: Ok, test done. XMLTEXT ist better than XMLELEMENT. Some REPLACE operations can be avoided. Thanks again.



Kind regards,
Bauer

[kolusu]

Thanks Bauer,

You did a great job in explaining the query.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu