Function

CREATE
OR REPLACE
FUNCTION getxmlattr(xml IN VARCHAR2,
xpath IN VARCHAR2) RETURN VARCHAR2 IS num NVARCHAR2(100);
BEGIN
SELECT
EXTRACTVALUE(SYS.XMLTYPE.CREATEXML(xml), xpath) INTO
num
FROM
dual;

RETURN num;
END getxmlattr;

Sample Query

SELECT getxmlattr(‘<author name=”John Smith”>’,’//author/@name’) AS NAME FROM DUAL;

Result

NAME
John Smith