| spooling xml column into one single row [message #587588] |
Mon, 17 June 2013 04:27  |
 |
perikaceo
Messages: 7
Registered: June 2013
|
Junior Member |
|
|
Hi,
I have been trying to spool a table into a .lst. The table is spooled correctly, but there is a column which has xmlType data into. The problem is it does not write the xml into a single row. Every time it find a xml node, the spooling gets indented.
Im quite new at this so any help would be appreciated
My code is as follows:
-- Establece opciones de formato.
set echo off;
set feedback off;
set heading off;
set recsep off;
set verify off;
set embedded off;
set long 1000000;
set pagesize 0;
SET LINESIZE unlimited;
set trimout off;
set trimspool on;
set serveroutput on size unlimited;
set term on;
-- Definicion de variables del script
prompt Introducir Creador de la tabla
prompt Creador de la tabla: &&1
prompt Introducir Nombre de la tabla
prompt Nombre de la tabla: &&2
prompt Introducir Directorio del fichero
prompt Directorio del fichero: &&3
prompt Introducir Nombre del fichero
prompt Nombre del fichero: &&4
prompt Introducir Caracter separador de campos
prompt Caracter separador de campos: &&5
prompt Campo fecha: &&6
-- Redirige la salida al fichero
spool /&&3/&&4
SELECT H5279_CD_EMP || '&&5' ||
H5279_LOCAL_ID || '&&5' ||
H5279_LOG_TYPE || '&&5' ||
H5279_GLOBL_ID || '&&5' ||
TO_CHAR(H5279_CREATED, 'YYYY-MM-DD HH24:MI:SS.FF3') || '&&5' ||
H5279_CALLERID || '&&5' ||
H5279_USR_ID || '&&5' ||
H5279_IPADDRES || '&&5' ||
H5279_CLLSERVN || '&&5' ||
H5279_NAMESCN || '&&5' ||
H5279_TYPESTT || '&&5' ||
H5279_NAMESTT || '&&5' ||
H5279_FUENTE || '&&5' ||
H5279_APPLOG || '&&5' ||
H5279_NOMBROP || '&&5' ||
H5279_NOMBROI || '&&5' ||
H5279_COMPONNT || '&&5' ||
H5279_MTODO || '&&5' ||
H5279_IDSERVDR || '&&5' ||
H5279_IDSSION || '&&5' ||
H5279_IDSESSEC || '&&5' ||
H5279_LOGDATA || '&&5' ||
H5279_ENTDDLOG
FROM
&&1..&&2
WHERE
TO_CHAR(H5279_CREATED, 'YYYYMMDD') = '&&6';
spool off
EXIT 0
where the field LOGDATA contains the xml. And the output im getting is as follows(I have attached the file)
0049|16052B0E44E3A30037E3CB59|4|16052B0E44E3BF0037D9CB4E|2013-06-12 11:51:16.387|16052B0E44E3A90037E2CB58|n31001|172.31.184.2|trxOpService|BDPESP_CIC_SAN_ENS_V1|||INTERNET|ALN_BDPGPO_Localizperscomun|| OI_LocalizarPersPorDocumentoComun|||ISWSBKSD149|frNixc6Vlic01jhixt5TkfT|16052A9553AE930037FAC740| <?xml version="1.0" encoding="ISO-8859-1"?>
<trxOpMessage transaction="PSFB" operation="3" version="00000">
<send>
<header>
<headerVersion>08</headerVersion>
<enterprise>0049</enterprise>
<center/>
<terminalId/>
<terminalType>J</terminalType>
<scrollIndexBegin/>
<scrollIndex/>
<logicalChannel>0001</logicalChannel>
<marketingChannel/>
<operationChannel/>
<internetUser/>
<trustIndicator>N</trustIndicator>
<userAddress>172.31.184.2</userAddress>
<language>E</language>
<logLevel>S</logLevel>
<firstLogToken>16052B0E44E3BF0037D9CB4E</firstLogToken>
<secondLogToken>16052B0E44E3A30037E3CB59</secondLogToken>
<deactivateBLIndicator>N</deactivateBLIndicator>
<occurrencesSeparator>¿</occurrencesSeparator>
<separator>¿</separator>
</header>
Any idea how to set it in a single row??
Thanks a lot
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
