Archivos Mensuales: diciembre 2014

PL/SQL generate XML output

Para generar XML desde PL/SQL existen diversos métodos que van desde una select que utilice funciones XML_TYPE al empleo de los paquetes PL/SQL integrados en Oracle. En nuestro ejemplo vamos a utilizar DBMS_XMLDOM para montar un XML como este:

<?xml version="1.0" encoding="ISO-8859-1"?>
<root>
  <node01 Attr01="Atribute 01" Attr02="Atribute 02">Node 01 text</node01>
  <node02>Node 02 text</node02>
</root>

Paquete de utilidades XML

El primer paso consiste en compilar el siguiente paquete en la base de datos. Esto nos va a permitir agilizar la creación de los nodos utilizando una serie de tablas para registrar cada nodo y el procedimiento de de escritura de estos nodos en el XML que estemos generando:

CREATE OR REPLACE PACKAGE create_xml IS
 
-- Declare XML types
   type ry_xmlattrib is record (
                                p_attrib_name     varchar2(512),
                                p_attrib_value    varchar2(32767)
                               );
   type ty_xmlattrib is table of ry_xmlattrib;
 
   type ry_xmlelement is record (
                                 p_parent         dbms_xmldom.DOMNode,
                                 p_element_name   varchar2(512),
                                 p_element        dbms_xmldom.DOMElement,
                                 p_attribs        ty_xmlattrib,
                                 p_node           dbms_xmldom.DOMNode,
                                 p_text_value     varchar2(32767),
                                 p_text           dbms_xmldom.DOMText,
                                 p_textnode       dbms_xmldom.DOMNode
                                );
   type ty_xmlelement is table of ry_xmlelement;

   type ty_varchar2 is table of varchar2(32767) index by varchar2(64);
 

-- Add node to ty_xmlelement
    procedure add_node(
                       p_tb_xmlelements  in out  ty_xmlelement,
                       p_tb_index_nodes  in out  ty_varchar2,
                       p_index_id        in      varchar2,
                       p_parent_index_id in      varchar2,
                       p_node_name       in      varchar2,
                       p_text_value      in      varchar2,
                       p_attribs         in      ty_xmlattrib
                      );
-- Write node to XML
   procedure write_node(
                        p_domdoc    in out   dbms_xmldom.DOMDocument,
                        p_reg_nodo  in out   ry_xmlelement
                       );
 
END create_xml;

CREATE OR REPLACE PACKAGE BODY create_xml AS

-- Add node to ty_xmlelement
procedure add_node(
                   p_tb_xmlelements  in out  ty_xmlelement,
                   p_tb_index_nodes  in out  ty_varchar2,
                   p_index_id        in      varchar2,
                   p_parent_index_id in      varchar2,
                   p_node_name       in      varchar2,
                   p_text_value      in      varchar2,
                   p_attribs         in      ty_xmlattrib
                  )
is
begin

   -- Add node
   p_tb_xmlelements.extend;
   p_tb_index_nodes( p_index_id ) := p_tb_xmlelements.last;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs := create_xml.ty_xmlattrib();
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_parent := p_tb_xmlelements( p_tb_index_nodes( p_parent_index_id ) ).p_node;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_element_name := p_node_name;
   p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_text_value := p_text_value;
   
   -- Add attribs
   if p_attribs is not null then
      p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs := create_xml.ty_xmlattrib();
      for i_att in 1..p_attribs.count() loop
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs.extend();
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs(i_att).p_attrib_name := p_attribs(i_att).p_attrib_name;
          p_tb_xmlelements( p_tb_index_nodes( p_index_id ) ).p_attribs(i_att).p_attrib_value := p_attribs(i_att).p_attrib_value;
      end loop;
   end if;
   
end add_node;

-- Write node to XML
procedure write_node(
                     p_domdoc    in out   dbms_xmldom.DOMDocument,
                     p_reg_nodo  in out   ry_xmlelement
                    )
is
begin
   
   -- Write node
   p_reg_nodo.p_element := dbms_xmldom.createElement(p_domdoc, p_reg_nodo.p_element_name );
     
   if p_reg_nodo.p_attribs is not null then
       if p_reg_nodo.p_attribs.count() > 0 then
          for i_att in 1..p_reg_nodo.p_attribs.count() loop
             dbms_xmldom.setAttribute(
                                      p_reg_nodo.p_element, 
                                      p_reg_nodo.p_attribs(i_att).p_attrib_name, 
                                      p_reg_nodo.p_attribs(i_att).p_attrib_value
                                     );
          end loop;
       end if;
   end if;
     
   p_reg_nodo.p_node := dbms_xmldom.appendChild(p_reg_nodo.p_parent,dbms_xmldom.makeNode(p_reg_nodo.p_element));
   p_reg_nodo.p_text := dbms_xmldom.createTextNode(p_domdoc, p_reg_nodo.p_text_value );
   p_reg_nodo.p_textnode := dbms_xmldom.appendChild(p_reg_nodo.p_node,dbms_xmldom.makeNode(p_reg_nodo.p_text));

end write_node;

END create_xml;

Ejemplo

El siguiente procedimiento utiliza el paquete create_xml para generar el XML de ejemplo con el que se ha iniciado esta entrada:

-- Generate XML (example)
CREATE OR REPLACE PROCEDURE xml_example
IS
 
  l_domdoc dbms_xmldom.DOMDocument;
  l_root_node dbms_xmldom.DOMNode;
  
  tb_xmlelements create_xml.ty_xmlelement := create_xml.ty_xmlelement();
  tb_index create_xml.ty_varchar2;
  tb_attribs create_xml.ty_xmlattrib := create_xml.ty_xmlattrib();
    
  v_buffer varchar2(32767);
  
BEGIN
  
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;
  
   -- Set version
   dbms_xmldom.setVersion(l_domdoc, '1.0');
   dbms_xmldom.setCharset(l_domdoc, 'ISO-8859-1');
  
   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);
   tb_xmlelements.extend;
   tb_index('root') := tb_xmlelements.last;

   tb_xmlelements(tb_index('root')).p_attribs      := create_xml.ty_xmlattrib();
   tb_xmlelements(tb_index('root')).p_parent       := l_root_node;
   tb_xmlelements(tb_index('root')).p_element_name := 'root';
   tb_xmlelements(tb_index('root')).p_text_value   := '';
       
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('root')));
       
   -- Create a new node with text and attributes
   tb_attribs.extend();
   tb_attribs(1).p_attrib_name := 'Attr01';
   tb_attribs(1).p_attrib_value := 'Atribute 01';
  
   tb_attribs.extend();
   tb_attribs(2).p_attrib_name := 'Attr02';
   tb_attribs(2).p_attrib_value := 'Atribute 02';

   create_xml.add_node(tb_xmlelements, tb_index, 'node01', 'root', 'node01', 'Node 01 text', tb_attribs );
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('node01')));
       
   -- Create a additional node with text
   tb_attribs.delete;
   create_xml.add_node(tb_xmlelements, tb_index, 'node02', 'root', 'node02', 'Node 02 text', tb_attribs );
   create_xml.write_node(l_domdoc, tb_xmlelements(tb_index('node02')));

   -- Result
   dbms_xmldom.writeToBuffer(l_domdoc, v_buffer);
   dbms_output.put_line(v_buffer);
   dbms_xmldom.freeDocument(l_domdoc);
 
END xml_example;

Encoding

Hay que tener especial cuidado con la declaración del encoding del XML. La forma de declararlo varía dependiendo del destino al que se vaya a enviar el XML: salida dbms_output, un XMLType o un Lob. En algunos conextos, en lugar de utilizar el procedimiento dbms_xmldom.setCharset(l_domdoc, ‘ISO-8859-1’) hay que manipular el procedimiento setVersion de la siguiente forma:

dbms_xmldom.setVersion(l_domdoc, '1.0" encoding="ISO-8859-1');
Anuncios