Oracle XML problem - difference in 9i and 10g

From: <Neil.Danson_at_gmail.com>
Date: 15 Sep 2006 02:05:44 -0700
Message-ID: <1158311144.364086.41550@i42g2000cwa.googlegroups.com>

I work primarily on a database which manages XML documents. I've been looking into migrating some of our client applciation XML code to Oracle, but seem to be getting worrying results between Oracle 9i and 10g.

the following anonymous procedure should show why

set serveroutput on

DECLARE
--v_xml SYS.XMLTYPE;

  v_doc CLOB;

  v_parser DBMS_XMLPARSER.parser;

  v_xmldoc DBMS_XMLDOM.DOMDocument;

  v_xmlNodeList DBMS_XMLDOM.DOMNodeList;

  v_xmlpi DBMS_XMLDOM.DOMProcessingInstruction;

  i integer;

  data varchar2(2000);
BEGIN

• XMLTYPE created from a CLOB v_doc := '<?xml version="1.0"?><TABLE_NAME><?lbsref 123?><Warning>MY_TABLE</Warning></TABLE_NAME>';

  v_parser := DBMS_XMLPARSER.newParser;

  DBMS_XMLPARSER.setValidationMode(v_parser, FALSE);
  DBMS_XMLPARSER.setPreserveWhiteSpace(v_parser, TRUE);
  DBMS_XMLPARSER.parseClob(v_parser, v_doc);

  v_xmldoc := DBMS_XMLPARSER.getDocument(v_parser);

  v_xmlNodeList:= DBMS_XMLDOM.getElementsByTagName(v_xmldoc,'*');

  i := 0;

  while (i<DBMS_XMLDOM.getLength(v_xmlNodeList)) loop

          if
DBMS_XMLDOM.getNodeType(DBMS_XMLDOM.item(v_xmlNodeList,i))=DBMS_XMLDOM.PROCESSING_INSTRUCTION_NODE then

                v_xmlpi
:=DBMS_XMLDOM.makeProcessingInstruction(DBMS_XMLDOM.item(v_xmlNodeList,i));

		data:=DBMS_XMLDOM.getData(v_xmlpi);
  	end if;
	i:=i+1;
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(i)||' Nodes');

  end loop;

--DBMS_XMLDOM.writeToBuffer(v_xmldoc,data);
  DBMS_OUTPUT.PUT_LINE(data);

END;
/

If you run that on a 9i database you get the following output

1 Nodes
2 Nodes
3 Nodes
4 Nodes
lbsref 123

in SQLPlus

on a 10g DB you get

1 Nodes
2 Nodes

It appears that the Processing instructions are no longer counted. Ive tried this on Windows 9i, Linux 9i, Windows 10g and Linux 10g databases and the results are consistant - which would lead me to belive that it was an intentional change. If this is so, how would i go about extracting all the PI's from the document if its not possible to use the wildcard in getElementsByTagName()? I cant use the XMLTYPE as I need to update the docuement and from what i can see thats not possible unless i load into the dom using dbms_xmldom.

Any help would be extremly appreciated!

Neil Received on Fri Sep 15 2006 - 04:05:44 CDT