在ORACLE中使用DOM方式解析XML

XML格式：

<Message id="101"> 
                    <From>Montgomery Burns</From> 
                    <Text>Release the Hounds!</Text> 
</Message>

解析代码

CREATE OR REPLACE FUNCTION idAttributeOfDocElement(xmldoc VARCHAR2 ) 
RETURN VARCHAR2 IS 

    theXmlDoc xmldom.DOMDocument; 
    theDocElt xmldom.DOMElement; 
    retval      VARCHAR2(400); 
    XMLParseError EXCEPTION; 
    PRAGMA EXCEPTION_INIT( XMLParseError, -20100 ); 

    -- Local parse function keeps code cleaner. Return NULL if parse fails 
    FUNCTION parse(xml VARCHAR2) RETURN xmldom.DOMDocument IS 
      retDoc xmldom.DOMDocument; 
      parser xmlparser.Parser; 
    BEGIN 
      parser := xmlparser.newParser; 
      xmlparser.parseBuffer(parser,xml); 
      retDoc := xmlparser.getDocument(parser); 
      xmlparser.freeParser(parser); 
      RETURN retdoc; 
    EXCEPTION 
      -- If the parse fails, we'll jump here. 
      WHEN XMLParseError THEN 
        xmlparser.freeParser(parser); 
        RETURN retdoc; 
    END; 

BEGIN 
    -- Parse the xml document passed in the VARCHAR2 argument 
    theXmlDoc := parse(xmldoc); 
    -- If the XML document returned is not NULL... 
    IF NOT xmldom.IsNull(theXmlDoc) THEN 
      -- Get the outermost enclosing element (aka "Document Element") 
      theDocElt := xmldom.getDocumentElement(theXmlDoc); 
      -- Get the value of the document element's "id" attribute 
      retval      := xmldom.getAttribute(theDocElt,'id');

      -- Free the memory used by the parsed XML document 
      xmldom.freeDocument(theXmlDoc); 
      RETURN retval; 
    ELSE 
      RETURN NULL; 
    END IF; 
END;

上面这个是传入一个XML格式的字符串，然后进行解析，但是有一个局限，就是varchar2的长度为4000
如果再长了将会报错

所以可以传入一个CLOB，CLOB的长度为4G

CREATE OR REPLACE FUNCTION idAttributeOfDocElements(xmldoc in CLOB)
return varchar2
is
theXmlDoc xmldom.DOMDocument;
n1          xmldom.DOMNodeList;
len1        number(10);
len2        number(10);
v1          xmldom.DOMNode;
n2          xmldom.DOMNodeList;
attn        xmldom.DOMNode;
vretuval     varchar2(1000):='';
XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT( XMLParseError, -20100 );
    -- Local parse function keeps code cleaner. Return NULL if parse fails
    FUNCTION parse(xml CLOB) RETURN xmldom.DOMDocument IS
      retDoc xmldom.DOMDocument;
      parser xmlparser.Parser;
    BEGIN
      parser := xmlparser.newParser;
      xmlparser.ParseCLOB(parser,xml);
      retDoc := xmlparser.getDocument(parser);
      xmlparser.freeParser(parser);
      RETURN retdoc;
    EXCEPTION
       --If the parse fails, we''ll jump here.
      WHEN XMLParseError THEN
        xmlparser.freeParser(parser);
        dbms_output.put_line('errors');
       RETURN retdoc;
    END;

BEGIN
    -- Parse the xml document passed in the CLOB argument
    theXmlDoc := parse(xmldoc);
    -- If the XML document returned is not NULL...
    IF NOT xmldom.IsNull(theXmlDoc) THEN
      -- Get the outermost enclosing element (aka "Document Element")
      --theDocElt := xmldom.getDocumentElement(theXmlDoc);
      -- Get the value of the document element's "id" attribute
      n1:= xmldom.getElementsByTagName(theXmlDoc, 'HB');
      len1     := xmldom.getLength(n1);
      dbms_output.put_line(len1);
      --获得<HB></HB>节点的数量
    for i in 0 .. len1 - 1 loop
    --获得节点
      v1     := xmldom.item(n1, i);
      --获得该节点下所有的子节点
      n2     := xmldom.getChildNodes(v1);
      --获得子节点的数量
      len2 := xmldom.getLength(n2);
           for j in 0..len2-1 loop
        --dbms_output.put_line(len2);
               attn:=xmldom.item(n2,j);
               dbms_output.put_line(xmldom.getNodeValue(xmldom.getFirstChild(attn)));
           end loop;
end loop;
      -- Free the memory used by the parsed XML document
      xmldom.freeDocument(theXmlDoc);

      RETURN vretuval;
    ELSE
      RETURN vretuval;
    END IF;
END;
/
测试代码：

select idAttributeOfDocElements
(
'<A>
<HB><HBH>CA1306</HBH><C>SZXPEK</C><TIME>2007-07-31</TIME><USER>4001</USER><STYPE>1</STYPE><CWB>FAC5YAFAC5YAFAC5YA</CWB><PP>我是谁哦</PP></HB>
</A>'
) from dual;