Prev: Can DB2_ALL get any easier ?
Next: FYI: DB2 9.7.2 eGA with more SQL goodies to get off Oracle
From: brunoalsantos on 28 May 2010 09:57
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.
From: Tonkuma on 28 May 2010 10:56
Please try:
XMLGROUP( ..... ROOT "DATA")
From: brunoalsantos on 28 May 2010 14:15
On 28 maio, 11:56, Tonkuma <tonk...(a)fiberbit.net> wrote:
> Please try:
> XMLGROUP( ..... ROOT "DATA")

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.
From: Tonkuma on 28 May 2010 16:02
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)?
From: brunoalsantos on 28 May 2010 16:32
On 28 maio, 17:02, Tonkuma <tonk...(a)fiberbit.net> wrote:
> 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)?

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.
 |  Next  |  Last
Pages: 1 2
Prev: Can DB2_ALL get any easier ?
Next: FYI: DB2 9.7.2 eGA with more SQL goodies to get off Oracle