Extracting XML information using PL/SQL

Extracting XML information using PL/SQL

Since version 9, Oracle has had support for XML data built into the heart of the database. Sometimes however, it is not immediately obvious how to get data from an XML string, particularly if, like me, it's not something you do that often!


Here is a small code fragment which, given an XML 'string' (xml_char) extracts either some attribute values or node text :

DECLARE
  xml_char VARCHAR2(1024);
  xml      xmltype;
  v_type   VARCHAR2(32);
  v_server VARCHAR2(32);
  v_port   VARCHAR2(32);
BEGIN
  -- create dummy XML document
  xml_char := '<?xml version="1.0"?>' ||
              '<CONNECTION>' ||
              '   <MAPSERVER type="Arcims">' ||
              '      <HOST>uranus</HOST>' ||
              '      <PORT>5300</PORT>' ||
              '   </MAPSERVER>' ||
              '</CONNECTION>';
  -- convert to XML from char type
  xml := xmltype.createxml(xml_char);
  -- extract some elements
  v_type   := xml.EXTRACT('/CONNECTION/MAPSERVER/@type').getstringval();
  v_server := xml.EXTRACT('/CONNECTION/MAPSERVER/HOST/text()').getstringval();
  v_port   := xml.EXTRACT('/CONNECTION/MAPSERVER/PORT/text()').getstringval();
  -- display them
  DBMS_OUTPUT.PUT_LINE('Type   = ' || v_type);
  DBMS_OUTPUT.PUT_LINE('Server = ' || v_server);
  DBMS_OUTPUT.PUT_LINE('Port   = ' || v_port);
END;

Firstly an XML 'object' is created from the xml_char XML string, we can then call the 'EXTRACT' method to get the relevant attribute or node value.


Comments (0)


Add a Comment

Please login to comment.