Hi all,

DB2 LUW V9.5 Fp5.

I would like to transform a result set in a XML output.
Using my statement, I've got this result set ...

<DATA>
<MACHINE_ID>
000103E1DE54FA3CD3B21542
<DEPARTMENT_NAME>100000001-MAQUINA NAO CADASTRADA - REGIONAL:
HX30</DEPARTMENT_NAME>
<MACHINE_HOSTNAME>AF106692</MACHINE_HOSTNAME>
<MACHINE_ID>
</DATA>
<DATA>
<MACHINE_ID>
000103E1DDEC0923F4751549
<DEPARTMENT_NAME>110014205-INFO PREPARACAO DE DOCUMENTOS</
DEPARTMENT_NAME>
<MACHINE_HOSTNAME>PREDOC123692</MACHINE_HOSTNAME>
<MACHINE_ID>
</DATA>

....


I need an output that looks the following:

<DATA>
<MACHINE_ID>
000103E1DE54FA3CD3B21542
<DEPARTMENT_NAME>100000001-MAQUINA NAO CADASTRADA - REGIONAL: HX30</
DEPARTMENT_NAME>
<MACHINE_HOSTNAME>AF106692</MACHINE_HOSTNAME>
</MACHINE_ID>
<MACHINE_ID>
000103E1DDEC0923F4751549
<DEPARTMENT_NAME>110014205-INFO PREPARACAO DE DOCUMENTOS</
DEPARTMENT_NAME>
<MACHINE_HOSTNAME>PREDOC123692</MACHINE_HOSTNAME>

<MACHINE_ID>
</DATA>


In other words: I need to put a <data> tag before the first line of
the result set, and include a </data> after the last line.

My statement is:

select
XMLELEMENT(NAME DATA,
XMLELEMENT(NAME MACHINE_ID, T.MACHINE_ID,
XMLELEMENT(NAME DEPARTMENT_NAME, D.DEPARTMENT_NAME) ,
XMLELEMENT(NAME MACHINE_HOSTNAME, T.MACHINE_HOSTNAME)))

FROM T;


Thanks in advance.

Please try:
XMLGROUP( ..... ROOT "DATA")

On 28 maio, 11:56, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
Thanks Tonkuma, works fine here.

But I've got another question ..

My output has a large string , and I received the folowing message
from CLP.

DB29320W Output has been truncated.

How Can I resolves this warning? I used XMLSERIALIZE ( .. ) but the
result was the same ...

Regards,
Bruno.

What data type in the XMLSERIALIZE did you specified?

Was that still too long?
For example: CLOB.
(Default for CLOB is CLOB(1M). )

How about something like VARCHAR(1000)?

On 28 maio, 17:02, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
I specified CLOB ..

My statement ..

select
XMLSERIALIZE(
XMLGROUP(
T.MACHINE_ID,
D.DEPARTMENT_NAME,
T.MACHINE_HOSTNAME
OPTION ROOT "desktop") AS CLOB (50M))

FROM
ASSET.TBL_ASSET_MACHINE_ID T
INNER JOIN ASSET.TBL_ASSET_DEPARTMENT D ON (T.DEPARTMENT_ID =
D.DEPARTMENT_ID)

;

Result on CLP ..

......
NAME>BSARO1-006</MACHINE_HOSTNAME></
row><row><MACHINE_ID>0001023C44E759AF89131534</
MACHINE_ID><DEPARTMENT_NAME>RIO</
DEPARTMENT_NAME><MACHINE_HOSTNAME>RBOMR1-005</MACHINE_HOSTNAME></row>
DB29320W Output has been truncated.

Thanks.

On 28 mei, 22:32, brunoalsantos <brunoalsan... (AT) gmail (DOT) com> wrote:
Hello Bruno,

The message you're seeing is coming from the DB2 CLP (client), not
from the server itself. Could you use another client to retrieve the
same string? In the past, I used Java to export such large XML
documents from DB2.

Off topic: if you want some fun, try Groovy, you'll love it.

--
Frederik Engelen