Problema XML y External Tables Hola,
Tengo el siguiente xml:
<?xml version="1.0" encoding="ISO-8859-1"?>
<Lista>
<Registro>
<Numero_TAG>IFG34343</Numero_TAG>
<Operador_TAG>1</Operador_TAG>
<Tipo_TAG>1</Tipo_TAG>
<Categoria_TAG>1</Categoria_TAG>
<Status>1</Status>
<Hora_actualizacion>13:10:23</Hora_actualizacion>
<Fecha_actualizacion>28-03-2012</Fecha_actualizacion>
<Importe_saldo>9900</Importe_saldo>
</Registro>
</Lista>
Lo estoy intentando leer de esta manera:
CREATE TABLE LISTA_BLANCA_XML
(
Numero_TAG VARCHAR2(36),
Operador_TAG VARCHAR2(36),
Tipo_TAG number(2),
Categoria_TAG number(2),
Status number(2),
Hora_actualizacion date,
Fecha_actualizacion date,
Importe_saldo number(15,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY XTERN_LISTA_BLANCA
ACCESS PARAMETERS
(
records delimited by '</Registro>'
badfile XTERN_LISTA_BLANCA_LOGS:'00000316042012152223.xml. bad'
logfile XTERN_LISTA_BLANCA_LOGS:'00000316042012152223.xml. log'
fields
(
filler char(2000) terminated by '<Registro>',
Numero_TAG CHAR(80) enclosed by '<Numero_TAG>' and '</Numero_TAG>',
Operador_TAG CHAR(80) enclosed by '<Operador_TAG>' and '</Operador_TAG>',
Tipo_TAG CHAR(80) enclosed by '<Tipo_TAG>' and '</Tipo_TAG>',
Categoria_TAG CHAR(80) enclosed by '<Categoria_TAG>' and '</Categoria_TAG>',
Status CHAR(80) enclosed by '<Status>' and '</Status>',
Hora_actualizacion CHAR(80) enclosed by '<Hora_actualizacion>' and '</Hora_actualizacion>' DATE_FORMAT DATE MASK 'hh24:mi:ss',
Fecha_actualizacion CHAR(80) enclosed by '<Fecha_actualizacion>' and '</Fecha_actualizacion>' DATE_FORMAT DATE MASK 'dd/mm/yyyy',
Importe_saldo CHAR(80) enclosed by '<Importe_saldo>' and '</Importe_saldo>'
)
)
LOCATION ('00000316042012152223.xml')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
Pero al hacer la query :
select * from LISTA_BLANCA_XML
No me saca nada y en el log aparece:
Field Definitions for table LISTA_BLANCA_XML
Record format DELIMITED, delimited by </Registro>
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
FILLER CHAR (2000)
Terminated by "<Registro>"
Trim whitespace same as SQL Loader
NUMERO_TAG CHAR (80)
Enclosed by "<Numero_TAG>" and "</Numero_TAG>"
Trim whitespace same as SQL Loader
OPERADOR_TAG CHAR (80)
Enclosed by "<Operador_TAG>" and "</Operador_TAG>"
Trim whitespace same as SQL Loader
TIPO_TAG CHAR (80)
Enclosed by "<Tipo_TAG>" and "</Tipo_TAG>"
Trim whitespace same as SQL Loader
CATEGORIA_TAG CHAR (80)
Enclosed by "<Categoria_TAG>" and "</Categoria_TAG>"
Trim whitespace same as SQL Loader
STATUS CHAR (80)
Enclosed by "<Status>" and "</Status>"
Trim whitespace same as SQL Loader
HORA_ACTUALIZACION CHAR (80)
Date datatype DATE, date mask hh24:mi:ss
Enclosed by "<Hora_actualizacion>" and "</Hora_actualizacion>"
Trim whitespace same as SQL Loader
FECHA_ACTUALIZACION CHAR (80)
Date datatype DATE, date mask dd/mm/yyyy
Enclosed by "<Fecha_actualizacion>" and "</Fecha_actualizacion>"
Trim whitespace same as SQL Loader
IMPORTE_SALDO CHAR (80)
Enclosed by "<Importe_saldo>" and "</Importe_saldo>"
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field OPERADOR_TAG
KUP-04035: beginning enclosing delimiter not found
KUP-04101: record 1 rejected in file d:\Lista\00000316042012152223.xml
KUP-04021: field formatting error for field NUMERO_TAG
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file d:\Lista\00000316042012152223.xml
¿Alguien puede echarme un cable? No consigo sacar el fallo.
Un saludo y gracias de antemano
