Questions with xml file creation, based on table

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Mon, 14 Jan 2013 8:38 am

Guys, good morning!
I researched a lot on xml topics, however I haven't found answer to my doubts!
1st) I need to generate an xml file, with the result of a query of oracle.
This first step is already OK, however there are some fields that I do not have information (in the table, are as null) However I need this tag in the file.
When generating via command below, he simply ignores the null fields and does not create the tag I need.
I managed to be clear in the explanation of the doubt?

Code: Select all
create or replace procedure gerar_xml is

  v_file Utl_File.File_Type;
  v_xml  CLOB;

BEGIN
  DECLARE
 
    v_file Utl_File.File_Type;
    v_xml  CLOB;
    v_more BOOLEAN := TRUE;
 
    v_conteudo_arquivo sys.xmltype;
 
  BEGIN
    V_XML := DBMS_XMLQUERY.getXML('SELECT * FROM custom_xml');
   -- V_XML := DBMS_XMLGEN.getXMLType('SELECT * FROM custom_xml');
 
    V_FILE := UTL_FILE.fopen('C_RECEIVED_FILES', 'TESTE.XML', 'w');
 
    WHILE V_MORE LOOP
      UTL_FILE.PUT(V_FILE, SUBSTR(V_XML, 1, 32767));
   
      IF LENGTH(V_XML) > 32767 THEN
        V_XML := SUBSTR(V_XML, 32768);
      ELSE
        V_MORE := FALSE;
      END IF;
    END LOOP;
 
    UTL_FILE.fclose(V_FILE);
 
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM, 1, 255));
      Utl_File.FClose(v_file);
   
  END;

END;
2nd) the header of the xml file is being generated as follows: <? xml version = \"1.0\"? > <ROWSET> How I could change the tag " rowset " for a name of my preference?

Thanks in advance to all! =)
guterror

Poston Tue, 15 Jan 2013 6:48 am

Good morning, have you tried using the NVL. I wonder if he would not solve their first question? So you could treat the value as null.

As for the other question, I don't know if you can change the header. Let's see if some other fellow forum can help.

Att.,
Tinho
Location: São Paulo - SP

Poston Tue, 15 Jan 2013 10:33 am

Unfortunately the way you proposed gives error when generating the xml.
The quotes are finalized ... lol the command looked like this: V_XML: = DBMS_XMLQUERY. getXML (' SELECT DT, NUMLINHA, SKU, NVL (UNID_MANEJO, ''), FROM custom_xml ');

Follow msg error attached.


<? xml version = \"1.0\"? > <ERROR> oracle.xml.sql. OracleXMLSQLException: ORA-01756: quoted string not properly terminated </ERROR>
guterror

Poston Fri, 18 Jan 2013 8:53 am

Guys, I had a major breakthrough in the generation of the file and via select the result is acceptable. :)
However when trying to insert in a variable xclob of error in Annex: <? xml version = \"1.0\"? > <ERROR> oracle.xml.sql. OracleXMLSQLException: ORA-00911: invalid character </ERROR> below is the code I'm using:
Code: Select all
create or replace procedure gerar_xml is

  v_file Utl_File.File_Type;
  v_xml  CLOB;

BEGIN
  DECLARE
 
    v_file Utl_File.File_Type;
    v_xml  CLOB;
    v_more BOOLEAN := TRUE;
 
  BEGIN
   v_xml:= DBMS_XMLQUERY.getXML('select xmlelement("Confirmacaoseparacao",
                  xmlforest(teste1.clmcl,
                            teste1.codfilial,
                            teste1.dtcriacao,
                            teste1.atualizacao ),
                            xmlelement("OrdemCarga",
                            xmlforest(teste1.NumDoctoTrans),
                            xmlforest(teste1.NumOrdemCarga),
                            xmlforest(teste1.CodLocalSaida),
                            xmlforest(teste1.CodTransportadora),
                            xmlforest(teste1.NumRomaneio),
                            xmlforest(teste1.placaveiculo),
                xmlelement("Remessas",
                    xmlelement("Remessa",
                       xmlforest(teste1.numremessa),
                          xmlelement("Itens",
                            xmlelement("Item",
                            xmlforest(teste1.numlinha),
                            xmlforest(teste1.sku),
                            xmlforest(teste1.quantidade),
                            xmlforest(teste1.unidmanejo),
                            xmlforest(teste1.deposito),
                            xmlforest(teste1.tipoestoque),
                             xmlelement("Lotes",
                                xmlelement("Lote",
                                xmlforest(teste1.numlote),
                                xmlforest(teste1.qtdlote)
               )
                )
                 )
                  )
                   )
                      )
                      )
                            )
  from custom_xml teste1;');

    V_FILE := UTL_FILE.fopen('C_RECEIVED_FILES', 'TESTE.XML', 'w');
 
    WHILE V_MORE LOOP
      UTL_FILE.PUT(V_FILE, SUBSTR(V_XML, 1, 32767));
   
      IF LENGTH(V_XML) > 32767 THEN
        V_XML := SUBSTR(V_XML, 32768);
      ELSE
        V_MORE := FALSE;
      END IF;
    END LOOP;
 
    UTL_FILE.fclose(V_FILE);
 
  EXCEPTION
   
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM, 1, 255));
      Utl_File.FClose(v_file);
   
  END;

END;
does anyone know any solutions? :)
guterror

Poston Fri, 18 Jan 2013 9:03 am

Brother, tries to pass the TO_CLOB function to see if it works, in variable assignment:
Code: Select all
v_xml:= TO_CLOB(DBMS_XMLQUERY.getXML ... );
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Mon, 21 Jan 2013 8:43 am

Cheezburger network, Made his suggestion, but the error persists.
Follows the message!

Code: Select all
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00911: caractere inválido
</ERROR>
any other ideas? :)
guterror

Poston Mon, 21 Jan 2013 11:12 am

I tried to pull; within the query on the line below?
Code: Select all
from custom_xml teste1;'
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 21 Jan 2013 1:51 pm

rogenaro, I tried Yes, but of the following error:
Code: Select all
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: Caractere '(' não é permitido em um nome de tag XML.</ERROR>
I tried using the to_clob function but without success too! :(
guterror

Poston Mon, 21 Jan 2013 1:58 pm

[quote = " guterror "] rogenaro, I tried Yes, but of the following error:
Code: Select all
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: Caractere '(' não é permitido em um nome de tag XML.</ERROR>
I'vê tried using the to_clob function but without success too! :([/quote]
guterror

Poston Tue, 22 Jan 2013 8:50 am

Tries to put an alias to the column probably return is being generated automatically with the first name of the query string, which includes the character ' ('.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 28 Jan 2013 8:53 am

Good morning!!
Tried as follows:
Code: Select all
   v_xml:= DBMS_XMLQUERY.getXML('SELECT B.* from (select xmlelement("ConfirmacaoSeparacao",
                  xmlforest(Clmcl,
                            CodFilial,
                            DtCriacao,
                            Atualizacao ),
                            xmlelement("OrdemCarga",
                            xmlforest(NumDoctoTrans),
                            xmlforest(NumOrdemCarga),
                            xmlforest(CodLocalSaida),
                            xmlforest(CodTransportadora),
                            xmlforest(NumRomaneio),
                            xmlforest(PlacaVeiculo),
                xmlelement("Remessas",
                    xmlelement("Remessa",
                       xmlforest(NumRemessa),
                          xmlelement("Itens",
                            xmlelement("Item",
                            xmlforest(NumLinha),
                            xmlforest(Sku),
                            xmlforest(Quantidade),
                            xmlforest(UnidManejo),
                            xmlforest(Deposito),
                            xmlforest(TipoEstoque),
                             xmlelement("Lotes",
                                xmlelement("Lote",
                                xmlforest(NumLote),
                                xmlforest(QtdLote)
               )
                )
                 )
                  )
                   )
                      )
                      )
                            )
  from xml_gene) b');
but without success.
Is there any other way to be generating this return in xml? =(
guterror

Poston Mon, 28 Jan 2013 10:50 am

And in this way gives the same error?

Code: Select all
v_xml:= DBMS_XMLQUERY.getXML(
'
  select xmlelement
         ( "ConfirmacaoSeparacao"
         , xmlforest
           ( Clmcl
           , CodFilial
           , DtCriacao
           , Atualizacao
           )
         , xmlelement
           ( "OrdemCarga"
           , xmlforest(NumDoctoTrans)
           , xmlforest(NumOrdemCarga)
           , xmlforest(CodLocalSaida)
           , xmlforest(CodTransportadora)
           , xmlforest(NumRomaneio)
           , xmlforest(PlacaVeiculo)
           , xmlelement
             ( "Remessas"
             , xmlelement
               ( "Remessa"
               , xmlforest(NumRemessa)
               , xmlelement
                 ( "Itens"
                 , xmlelement
                   ( "Item"
                   , xmlforest(NumLinha)
                   , xmlforest(Sku)
                   , xmlforest(Quantidade)
                   , xmlforest(UnidManejo)
                   , xmlforest(Deposito)
                   , xmlforest(TipoEstoque)
                   , xmlelement
                     ( "Lotes"
                     , xmlelement
                       ( "Lote"
                       , xmlforest(NumLote)
                       , xmlforest(QtdLote)
                       )
                     )
                   )
                 )
               )
             )
           )
         ) resultado
  from xml_gene' );

rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 01 Feb 2013 8:42 am

Rogenaro, same problem.
What I did was insert solution the content in an xml table .type and get the whole return result.
:D

Thanks for the help!
guterror


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: Bing [Bot] and 4 guests