DBMS_XMLDOM is the W3C DOM API in PL/SQL. After DBMS_XMLPARSER parses a document into a tree of nodes, DBMS_XMLDOM walks the tree, reads elements, attributes, and text, and changes it: creating elements, appending children, and writing the result back to text.
Code for This Guide
The main examples are in the examples/pkg-xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
p := dbms_xmlparser.newparser; dbms_xmlparser.parsebuffer(p, xml_text); doc := dbms_xmlparser.getdocument(p); list := dbms_xmldom.getelementsbytagname(doc, 'tag'); elem := dbms_xmldom.makeelement(dbms_xmldom.item(list, i)); -- i from 0 dbms_xmldom.getattribute(elem, 'name') elem := dbms_xmldom.createelement(doc, 'tag'); node := dbms_xmldom.appendchild(parent_node, child_node); dbms_xmldom.writetobuffer(doc, text_out); dbms_xmldom.freedocument(doc);
Nodes, elements, and text nodes are different types; MAKENODE and MAKEELEMENT convert between them.
Read and Extend a Crew List
Example:
declare
v_parser dbms_xmlparser.parser := dbms_xmlparser.newparser;
v_doc dbms_xmldom.domdocument;
v_list dbms_xmldom.domnodelist;
v_elem dbms_xmldom.domelement;
v_new dbms_xmldom.domnode;
v_out varchar2(4000);
begin
dbms_xmlparser.parsebuffer(v_parser,
'<crew flight="NM150"><member role="CPT">Anil Rao</member>'
|| '<member role="FO">Mei Lin</member></crew>');
v_doc := dbms_xmlparser.getdocument(v_parser);
dbms_xmlparser.freeparser(v_parser);
v_list := dbms_xmldom.getelementsbytagname(v_doc, 'member');
for i in 0 .. dbms_xmldom.getlength(v_list) - 1 loop
v_elem := dbms_xmldom.makeelement(dbms_xmldom.item(v_list, i));
dbms_output.put_line(dbms_xmldom.getattribute(v_elem, 'role') || ': '
|| dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(dbms_xmldom.makenode(v_elem))));
end loop;
-- add a member: create the element and its text, and append them
v_elem := dbms_xmldom.createelement(v_doc, 'member');
dbms_xmldom.setattribute(v_elem, 'role', 'FA');
v_new := dbms_xmldom.appendchild(dbms_xmldom.makenode(v_elem),
dbms_xmldom.makenode(dbms_xmldom.createtextnode(v_doc, 'Sara Kim')));
v_new := dbms_xmldom.appendchild(
dbms_xmldom.makenode(dbms_xmldom.getdocumentelement(v_doc)),
dbms_xmldom.makenode(v_elem));
dbms_xmldom.writetobuffer(v_doc, v_out);
dbms_output.put_line(v_out);
dbms_xmldom.freedocument(v_doc);
end;
/Output:
CPT: Anil Rao FO: Mei Lin <crew flight="NM150"> <member role="CPT">Anil Rao</member> <member role="FO">Mei Lin</member> <member role="FA">Sara Kim</member> </crew> PL/SQL procedure successfully completed.
- GETELEMENTSBYTAGNAME finds both member elements, and the loop reads each role attribute and the text in the element's first child node.
- A new member element gets a role attribute and a text node, and is appended to the root element.
- WRITETOBUFFER returns the changed document, now with three members.
Things to Know
- Free parsers and documents with FREEPARSER and FREEDOCUMENT; DOM trees use session memory until freed.
- Node lists are zero-based.
- For reading values only, XMLTABLE in SQL is shorter; DOM is for building and changing documents node by node.
Related Guides
Conclusion
DBMS_XMLPARSER and DBMS_XMLDOM parse XML into a node tree that PL/SQL can read and change element by element. Convert between node types with MAKENODE and MAKEELEMENT, and free documents when done.
