oracle获取xml节点数据
1.新增获取节点函数
CREATE OR REPLACE FUNCTION get_xml_nodeValue(xmlStr CLOB,
nodeName VARCHAR2)
RETURN VARCHAR2 IS
xmlPar xmlparser.Parser := xmlparser.newParser;
xDoc xmldom.DOMDocument;
lenItme INTEGER;
itemNodes xmldom.DOMNodeList;
itemNode xmldom.DOMNode;
valueReturn VARCHAR2(100);
BEGIN
xmlparser.parseClob(xmlPar, xmlStr);
xDoc := xmlparser.getDocument(xmlPar);
xmlparser.freeParser(xmlPar);
itemNodes := xmldom.getElementsByTagName(xDoc, nodeName);
lenItme := xmldom.getLength(itemNodes);
IF lenItme = 0 THEN
valueReturn := '';
ELSE
itemNode := xmldom.item(itemNodes, 0);
valueReturn := xmldom.getNodeValue(xmldom.getFirstChild(itemNode));
END IF;
xmldom.freeDocument(xDoc);
RETURN valueReturn;
END get_xml_nodeValue;
2plsql直接用法
select get_xml_nodeValue(b.content, 'PATIENT_ID')
from xxxxx b