Parse XML file which in BLOB data type via XPATH

最新推荐文章于 2024-02-25 19:21:55 发布

原创最新推荐文章于 2024-02-25 19:21:55 发布 · 129 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#XML #Oracle

技术摘要专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种在Oracle 10g中通过XPath解析存储于BLOB类型字段中的XML文件的方法。提供了一个PL/SQL函数，该函数接受BLOB类型的参数及XPath表达式，并返回对应的XML值。

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;