第1个回答 2015-03-20
一 查询语句的结果转换成XML
1 创建函数用于转换
CREATE OR REPLACE FUNCTION get_query_xml (
q_string IN VARCHAR2, --查询语句
p_rowtag IN VARCHAR2 DEFAULT NULL --XML标签
)
RETURN CLOB
IS
ctx NUMBER;
xmldoc CLOB;
page NUMBER := 0;
xmlpage BOOLEAN := TRUE;
refcur sys_refcursor;
BEGIN
OPEN refcur FOR q_string;
ctx := DBMS_XMLGEN.newcontext (refcur);
DBMS_LOB.createtemporary (xmldoc, TRUE);
IF NOT (p_rowtag IS NULL)
THEN
DBMS_XMLGEN.setrowtag (ctx, p_rowtag);
END IF;
xmldoc := DBMS_XMLGEN.getxml (ctx, DBMS_XMLGEN.NONE);
DBMS_XMLGEN.closecontext (ctx);
RETURN xmldoc;
END;
2 调用此函数生成XML
select get_query_xml('SELECT ITEM,SHORT_DESC FROM ITEM_MASTER WHERE ITEM_PARENT = ''300010571'' ','DATA') from dual;
二 解析XML 数据
SELECT * FROM XMLTABLE('$B/ROWSET/DATA' PASSING
XMLTYPE('<?xml version="1.0"?>
<ROWSET>
<DATA>
<ITEM>500000000</ITEM>
<SHORT_DESC>162139802</SHORT_DESC>
</DATA>
</ROWSET>
') AS B
COLUMNS item VARCHAR2(500) PATH '/DATA/ITEM',
short_desc VARCHAR2(500) PATH '/DATA/SHORT_DESC'
)本回答被网友采纳