Parse XML file which in BLOB data type via XPATH

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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值