Problem Inserting an XML file in BD

[patricia]

When you run the following procedure in Oracle Database 10g Express Edition:

Select all

DECLARE 
b_Arquivo BFILE; 
conteudo CLOB;  
BEGIN 
b_Arquivo := bfilename('MY_XML', 'perfil.XML'); 
DBMS_LOB.fileOpen(b_Arquivo, dbms_lob.file_readonly); 
DBMS_LOB.createtemporary(conteudo, TRUE, DBMS_LOB.session); 
DBMS_LOB.loadFromFile(conteudo,b_Arquivo,DBMS_LOB.getLength(b_Arquivo),1,1); 
DBMS_LOB.fileClose(b_Arquivo); 
INSERT INTO TPerfil(IdDoc, Perfis) 
VALUES(S_TPerfil_IdDoc.NEXTVAL, XMLType.createxml(conteudo)); 
END; 
 
Ocorre o seguinte erro: 
ORA-31011: falha no parse XML 
ORA-19202: Ocorreu um erro no processamento XML 
LPX-00210: esperava '<' em vez de '?' 
Error at line 11. DECLARE 
2. v_Arquivo bfile; 
3. conteudo CLOB;

I checked the structure of the XML file and not on issues as follows:

Select all

<?xml version"1.0" encoding"utf-8" ?> 
<PERFIS> 
   <DESCRICAO> Cadastro de Perfil </DESCRICAO> 
   <PERFIL ID="1"> 
      <NOME> Rafael Lisboa Carvalho </NOME> 
      <PROFISSAO> Administrador de Redes </PROFISSAO > 
      <EMAIL> admin@lasalle.tche.br </EMAIL> 
      <CIDADE> Porto Alegre </CIDADE> 
      <ESTADO> Rio Grande do Sul </ESTADO> 
   </PERFIL> 
   <PERFIL ID="2"> 
      <NOME> Carlos Alberto Mendes </NOME> 
      <PROFISSAO> Analista de Sistemas </PROFISSAO> 
      <EMAIL> cmendes@lasalle.tche.br </EMAIL> 
      <CIDADE> Santa Cruz </CIDADE> 
      <ESTADO> Rio Grande do Sul </ESTADO> 
   </PERFIL> 
   <PERFIL ID="3"> 
      <NOME> Adriana Aguiar </NOME> 
      <PROFISSAO> Web Designer </PROFISSAO> 
      <EMAIL> aaguiar@lasalle.tche.br </EMAIL> 
      <CIDADE> Campinas </CIDADE> 
      <ESTADO> São Paulo </ESTADO> 
   </PERFIL> 
</PERFIS>

Thanks.
Patricia

[gilbertoca]

Hi Patricia! Apologies for the delay!
I help in the forum, just at my lunch or on weekends!

Your XML document is bad formed.

Select all

 
<?xml version"1.0" encoding"utf-8"?> 

must be:

Select all

 
<?xml version="1.0" encoding="utf-8"?> 

until,

Gilberto

[alegyn]

Good afternoon, colleagues.

On one of my forum accesses I decided to verify the procedure in question. However, when creating the table in Oracle, the procedure, and use the XML file with the same structure as Mr Patricia, it still gave an error to the wheel in SQL * Plus Worksheet. Below is the error reported by the application.

Select all

ERRO na linha 1: 
ORA-22285: non-existent directory or file for FILEOPEN operation 
ORA-06512: at "SYS.DBMS_LOB", line 504 
ORA-06512: at "SAMA.SPXML", line 7 
ORA-06512: at line 1

Hugs.

[Trevisolli]

Brother, Next ... Check the following situations:

1) Does the directory exist?
2) If yes, do you have Read Grant in this Directory object?

Whatever it sends there.

[leandromiranda87]

Good afternoon, I have a problem in a code quite similar to this one of the first example:

Select all

create table custom_teste_xml (teste sys.xmltype) 
 
CREATE OR REPLACE PROCEDURE ArmazenarXML AS 
conteudo CLOB := ' '; 
fonte bfile:= bfilename('D:\exercicios_001\Exercicios', 'contato001.xml'); 
 
BEGIN 
DBMS_LOB.fileOpen(fonte, DBMS_LOB.file_readonly); 
DBMS_LOB.loadFromFile(conteudo, fonte, DBMS_LOB.getLength(fonte), 1, 1); 
DBMS_LOB.fileClose(fonte); 
INSERT INTO custom_teste_xml(teste) VALUES(SYS.XMLType.createXML(conteudo)); 
 
END ArmazenarXML;

The problem is that at the time of the Execution The "Source" variable receives the value

Select all

'D:\exercicios_001\Exercicios/contato001.xml'

with A / to the right, while it should be the \

I use Oracle 11g , Windows XP and PL / SQL Developer

can help me?

[leandromiranda87]

I was able to read, but not the importation of the information.

Reading has been corrected by creating a directory, and calling this directory in high box.

Select all

CREATE OR REPLACE PROCEDURE ArmazenarXML AS 
  
AuxNomeDiretorio varchar2(100) := 'DIRETORIO_TESTE';  
AuxNomeArquivo varchar2(100) := 'contato001.xml';  
AuxArquivo bfile;  
AuxConteudo CLOB;  
AuxXML xmltype;  
begin  
-- create or replace directory Diretorio_teste as 'D:\exercicios_001';  
--  
AUXArquivo := bfilename(AuxNomeDiretorio,AuxNomeArquivo);  
--  
sys.DBMS_LOB.fileOpen(AUXArquivo,sys.dbms_lob.file_readonly);  
--  
sys.DBMS_LOB.createtemporary(AuxConteudo,TRUE,sys.DBMS_LOB.session);  
--  
sys.DBMS_LOB.loadFromFile(AuxConteudo,AuxArquivo,sys.DBMS_LOB.getLength(AuxArquivo),1,1);  
--  
sys.DBMS_LOB.fileClose(AuxArquivo);  
--  
AuxXML := XMLType.createxml(AuxConteudo);  
--  
Insert Into custom_teste_xml(teste) values(AuxXML);  
commit;  
end ArmazenarXML; 

The XML file got on Rodrigo Almeida's website, but I could not post here .. The forum said is spam

] But I had the same result without the encoding parameter, or even with other known parameters.
Errors:

Select all

ORA-31011: falha no parse XML 
ORA-19202: Ocorreu um erro no processamento XML  
LPX-00210: esperava '<' em vez de '?' 

I believe this is because the text of the XML file is not translated correctly or is not in the correct pattern.

Do you know if that's the problem?

Thanks.

[leandromiranda87]

Hello,

Once again changes in the code was made and in this way I was able to import XML at the base.

Select all

CREATE OR REPLACE PROCEDURE ArmazenarXML AS 
BEGIN 
 
insert into custom_teste_xml (teste) values (xmltype(bfilename('DIRETORIO_TESTE','contato001.xml'),nls_charset_id('AL32UTF8'))); 
 
END ArmazenarXML;

The main change was the insertion of the format of the XML file: NLS_CHARSET_ID ('AL32UTF8')

If the problem was solved.