Extract value, Procedure

Question

Initially I needed to create a precedent that would read an xml file and put the data in an oracle table, create the table, the directory, I have the following structure:

create or replace package body PACK_GUSTAVO1 as

v_id            INFO_XML.ID%type;
cursor C1 is    SELECT NVL(MAX(ID)+1,1) AS ID FROM INFO_XML;

PROCEDURE IMPORTA_XML IS
BEGIN

/* ABRE CURSOR */
    OPEN C1;
    LOOP
        /* LÊ UM REGISTRO DO CURSOR */
        fetch C1 into v_id;
         /* ABANDONA O LOOP CASO SEJA O FINAL DO CURSOR */
        EXIT WHEN C1%NOTFOUND;
    END LOOP;
    /* FECHA O CURSOR */
    CLOSE C1;

DBMS_OUTPUT.PUT_LINE('proximo:' || v_id);

DELETE FROM INFO_XML WHERE ID=v_id;
COMMIT;

INSERT INTO INFO_XML (ID, CONTEUDO) VALUES
    (v_id, XMLTYPE(BFILENAME('CARGA_CSV', 'funcionarios.xml'), NLS_CHARSET_ID('WE8ISO8859P1')));
COMMIT;    

/* grava na tabela */

END;

end PACK_GUSTAVO1;

and my select Extract value:

SELECT extractValue(CONTEUDO, '/funcionarios/contato/nome/value') as NOME FROM INFO_XML;

but it’s making a mistake, I don’t know what to do.

Answer 1

I came to the following conclusion, through a link I found from TOAD, posted in the comments. I am currently here, and what I am solving is the issue of my xml file appears with Japanese font in my table, I believed it was the xml header, change it to UTF-8, so that it could read in my dialect... but came again the Japanese font, or Chinese, I don’t know, rs' So I decided to send the real files I had here.

create or replace PACKAGE BODY TESTE2 AS


-- PARAMETROS PARA TESTE : CARGA_CSV , funcionarios.xml

PROCEDURE XXX( dir VARCHAR2, file VARCHAR2, name VARCHAR2 := NULL)  IS
 theBFile   BFILE;
  theCLob    CLOB;
  theDocName VARCHAR2(200) := NVL(name,file);
BEGIN 
DELETE FROM xml_documents;
    COMMIT;

  INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob())
  RETURNING xmldoc INTO theCLob;

  -- (3) Get a BFile handle to the external file
  theBFile := BFileName(dir,file);

  -- (4) Open the file
  dbms_lob.fileOpen(theBFile);

  -- (5) Copy the contents of the BFile into the empty CLOB
  dbms_lob.loadFromFile(dest_lob => theCLob,
                         src_lob => theBFile,
                         amount  => dbms_lob.getLength(theBFile));

  -- (6) Close the file and commit
  dbms_lob.fileClose(theBFile);  


DBMS_OUTPUT.PUT_LINE('Salvo com Sucesso !!!');

END XXX;


END TESTE2;