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');
desc EMP 