Import XML to Oracle tables

Asked

Viewed 3,407 times

0

. Hello,

I need to import an XML file to 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

Follows 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

Call from Procedure load_xml

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

And then the mistake 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

It’s been a week since I’ve been able to progress with this task, and if anyone has any ideas, they’re welcome. After this storage, still comes the extractvalue part of the xmltype column of the table by inserting the tags strings in another table, but I find it unnecessary to post here and I could not even complete this first part.

Thank you, Abs.

sql xml oracle pl-sql

  • Tried to open this file with another program that reads xml ?

  • I tried yes Motta, I changed the whole trial and it worked. I found several errors during the construction, but it worked. I will post here the result so that, who knows, someone can use. Thank you!!

1 answer

0


. Dear, Follow the solution I found. If someone faces the same problems, you can use the 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 Database;
  • Namegxml.xml = Name of . xml that should be in the created directory path;
  • NLS_CHARSET_ID('WE8ISO8859P1') = Encoding of my XML is Isolatino, so your Id.

=)

Browser other questions tagged sql xml oracle pl-sql

You are not signed in. Login or sign up in order to post.