您将需要使用CLOB作为XMLTYPE()的输入,而不是VARCHAR。
使用dbms_lob.loadclobfromfile
从文件中加载xml,或通过将xml分成32000个字符块并附加到CLOB的方式。
DECLARE
xmlClob CLOB;
BEGIN
/* Build Clob here */
WITH openedXml AS (
SELECT extractvalue(column_value, '/theRow/First') FIRST,
extractvalue(column_value, '/theRow/Last') LAST,
to_number(extractvalue(column_value, '/theRow/Age')) Age
FROM TABLE(XMLSequence(XMLTYPE(xmlClob).extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;
END;