How to parse XML file which in BLOB data type via XPATH in Oracle 10g
The following function can parse the XML file which stored in Oracle BLOB type column via XPATH.
The function has 2 input parameters:
V_ATTACHMENT: This parameter should pass in the BLOB column, if the column is in CLOB type, you can change the function, set the data type to CLOB and modify the 1st query SELECT FN_BLOB2CLOB(v_ATTACHMENT) INTO v_CLOB FROM DUAL to SELECT v_ATTACHMENT INTO v_CLOB FROM DUAL
V_EXPRESSION: This parameter is to pass in the XPATH Expression.
The function has 1 return value in VARCHAR2 type.
CREATE OR REPLACE FUNCTION DMTA.FN_PARSE_XPATH(v_ATTACHMENT IN BLOB, v_EXPRESSION IN VARCHAR2)
RETURN VARCHAR2 IS
v_XML XMLType;
v_XML2 XMLType;
v_CLOB CLOB;
BEGIN
SELECT FN_BLOB2CLOB(v_ATTACHMENT) INTO v_CLOB FROM DUAL;
v_XML := sys.XMLType.createXML(v_CLOB);
SELECT EXTRACT(v_XML,v_EXPRESSION) INTO v_XML FROM DUAL;
RETURN v_XML.getStringVal();
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
The following function can parse the XML file which stored in Oracle BLOB type column via XPATH.
The function has 2 input parameters:
V_ATTACHMENT: This parameter should pass in the BLOB column, if the column is in CLOB type, you can change the function, set the data type to CLOB and modify the 1st query SELECT FN_BLOB2CLOB(v_ATTACHMENT) INTO v_CLOB FROM DUAL to SELECT v_ATTACHMENT INTO v_CLOB FROM DUAL
V_EXPRESSION: This parameter is to pass in the XPATH Expression.
The function has 1 return value in VARCHAR2 type.
CREATE OR REPLACE FUNCTION DMTA.FN_PARSE_XPATH(v_ATTACHMENT IN BLOB, v_EXPRESSION IN VARCHAR2)
RETURN VARCHAR2 IS
v_XML XMLType;
v_XML2 XMLType;
v_CLOB CLOB;
BEGIN
SELECT FN_BLOB2CLOB(v_ATTACHMENT) INTO v_CLOB FROM DUAL;
v_XML := sys.XMLType.createXML(v_CLOB);
SELECT EXTRACT(v_XML,v_EXPRESSION) INTO v_XML FROM DUAL;
RETURN v_XML.getStringVal();
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RETURN NULL;
END;