XML import for Oracle tables

Question

XML import for Oracle tables

Navigation

#1 by (0 votes)

0

. Hello,

I need to import an XML file into an oracle table. There are several examples on the internet, but none of them worked for me. In general, I'm having the error:

LPX-00210: expected '<' instead of '�'
ORA-06512: at "SYSTEM.LOAD_XML", line 16

Follow procedures:

XML Example

<?xml version="1.0" encoding="UTF-8"?>
<funcionarios>
<funcionario>
    <CodUsu>PRV120</CodUsu>
    <NomFunci>TESTE DA SILVA</NomFunci>
    <DataPosse>01/01/2001</DataPosse>
    <CodGerencia>67</CodGerencia>
    < --MUITO MAIS INFORMAÇÃO ... -->
</funcionario>
<funcionario>
    <CodUsu>PRV121</CodUsu>
    <NomFunci>TESTE DA SILVA1</NomFunci>
    <DataPosse>01/12/2001</DataPosse>
    <CodGerencia>67</CodGerencia>
    < --MUITO MAIS INFORMAÇÃO ... -->
</funcionario>
<funcionario>
    <CodUsu>PRV122</CodUsu>
    <NomFunci>TESTE DA SILVA2</NomFunci>
    <DataPosse>28/06/2001</DataPosse>
    <CodGerencia>67</CodGerencia>
    < --MUITO MAIS INFORMAÇÃO ... -->
</funcionario>
</funcionarios>

Table that will store the XML to be read

CREATE TABLE INFO_XML
(ID NUMBER,
DATA_ALTERACAO DATE,
CONTEUDO XMLTYPE)

Importing XML into the INFO_XML table

CREATE OR REPLACE PROCEDURE load_xml ( p_id IN NUMBER,
p_filename IN VARCHAR2) AS
l_bfile BFILE := BFILENAME( 'XML_FILE', p_filename);
targetfile BFILE;
l_clob CLOB;
BEGIN

targetfile := l_bfile;

DBMS_LOB.createtemporary (l_clob, TRUE);

DBMS_LOB.fileopen(targetfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, targetfile, DBMS_LOB.getlength(targetfile));
DBMS_LOB.fileclose(targetfile);

INSERT INTO INFO_XML (id, data_atualizacao, conteudo)
VALUES (p_id, sysdate, XMLTYPE.createXML(l_clob));

DBMS_LOB.freetemporary (l_clob);
END;

- Where XML_FILE is my directory created in ORACLE indicating the physical path of .xml

Procedure call load_xml

BEGIN
load_xml(p_id=>1, p_filename => 'Teste.xml');
END;

And then the error I mentioned:

Error

ORA-31061: XDB error: XML event error
ORA-19202: Error occurred in XML processing
In line 1 of orastream:
LPX-00210: expected '<' instead of '�'
ORA-06512: at "SYSTEM.LOAD_XML", line 16
ORA-06512: at line 2

I have not been able to progress with this task for a week, so anyone who has any ideas will be very welcome. After this storage, it still comes the extractvalue part of the xmltype column of the table inserting the strings of the tags into another table, but I find it unnecessary to post here being that I could not even finish that first part.

Thank you, Abs.

sql oracle xml pl-sql

asked by anonymous 30.06.2014 / 00:32

1 answer

How to send ListT with KvmSerializable? Remove Items Duplicate ListView C #

score 0 · Accepted Answer

. Expensive, Here is the solution I found. If you face the same problems, you can set an example.

PROCEDURE IMPORTA_XML AS

BEGIN

  -- INSERE NA TABELA INFO_XML O .XML INTEIRO EM UMA COLUNA XMLTYPE

  INSERT INTO INFO_XML (ID, DATA, CONTEUDO) VALUES
    ((SELECT ID+1 FROM INFO_XML WHERE ID = (SELECT MAX(ID) FROM INFO_XML)),
    SYSDATE,
    XMLTYPE(BFILENAME('PPM_FILE_DIR', 'NomeXML.xml'), NLS_CHARSET_ID('WE8ISO8859P1')));
    DBMS_OUTPUT.PUT_LINE ('INTEGRACAO_RH_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.XML INSERIDO EM INFO_XML');
  COMMIT;


  -- EXTRAI DA TABELA INFO_XML O ARQUIVO E LE COMO STRING SUAS TAGS RELACIONADAS ABAIXO.
  INSERT INTO VIEW_AQF (codUsu, NOMFUNCI, PRIMEIRONOME, NOMEMEIO, SOBRENOME, DSCEMAILPROPOS, CODCARGO,
    DSCCARGOPREVI, CODDIRETORIA, DIRETORIA, CODGERENCIA, GERENCIA, CODNUCLEO, NUCLEO, DATAPOSSEPREVI,
    NUMTEL2FUNCI, GERENTE, /*DATALTER,*/ INDATIVO)
    SELECT DISTINCT
    LOWER(EXTRACTVALUE (value(func), '/funcionario/codUsu')) codUsu,
    EXTRACTVALUE (value(func), '/funcionario/nomFunci') nomFunci,
    EXTRACTVALUE (value(func), '/funcionario/primeiroNome') primeiroNome,
    EXTRACTVALUE (value(func), '/funcionario/nomeMeio') nomeMeio,
    EXTRACTVALUE (value(func), '/funcionario/sobrenome') sobrenome,
    EXTRACTVALUE (value(func), '/funcionario/dscEmailPropos') dscEmailPropos,
    EXTRACTVALUE (value(func), '/funcionario/codCargo') codCargo,
    EXTRACTVALUE (value(func), '/funcionario/dscCargoPrevi') dscCargoPrevi,
    EXTRACTVALUE (value(func), '/funcionario/codDiretoria') codDiretoria,
    EXTRACTVALUE (value(func), '/funcionario/diretoria') diretoria,
    nvl(EXTRACTVALUE (value(func), '/funcionario/codGerencia'), 125) codGerencia,
    nvl(EXTRACTVALUE (value(func), '/funcionario/gerencia'), 'GTEST') gerencia,
    nvl(EXTRACTVALUE (value(func), '/funcionario/codNucleo'), 69) codNucleo,
    nvl(EXTRACTVALUE (value(func), '/funcionario/nucleo'), 'DTIJR') nucleo,
    EXTRACTVALUE (value(func), '/funcionario/dataPossePrevi') dataPossePrevi,
    EXTRACTVALUE (value(func), '/funcionario/numTel2Funci') numTel2Funci,
    EXTRACTVALUE (value(func), '/funcionario/gerente') gerente,
    --EXTRACTVALUE (value(func), '/funcionario/datAlter') datAlter,
    EXTRACTVALUE (value(func), '/funcionario/indAtivo') indAtivo
    FROM INFO_XML, TABLE (XMLSEQUENCE(EXTRACT(conteudo, '/funcionarios/funcionario'))) func
    WHERE ID = (SELECT MAX(ID) FROM INFO_XML);
    DBMS_OUTPUT.PUT_LINE ('IMPORTADO PARA VIEW_AQF EM:'||SYSDATE);
  COMMIT;

END;

Where:

PPM_FILE_DIR = Directory created in the Bank;
XMLName.xml = Name of the .xml that should be in the directory path created;
NLS_CHARSET_ID ('WE8ISO8859P1') = Encoding of my XML is ISOLatino, then its Id

=)