CREATE OR REPLACE FUNCTION GET_XML_RECORD
(XMLSQL IN VARCHAR2,
ENCODINGTYPE IN VARCHAR2 := 'UTF-8') RETURN CLOB IS
CTX DBMS_XMLQUERY.CTXHANDLE;
XMLRECORD CLOB;
BEGIN
-- Get Context
CTX := DBMS_XMLQUERY.NEWCONTEXT(XMLSQL);
-- Encoding Setting
DBMS_XMLQUERY.SETENCODINGTAG(CTX, ENCODINGTYPE);
-- Make XML
XMLRECORD := DBMS_XMLQUERY.GETXML(CTX);
RETURN xmlRecord;
END GET_XML_RECORD;
--------------------------------------------------------------------------------------
결과물(Result) XML
--------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<ROWSET>
<ROW num="1">
<BU>111100</BU>
<PART>Foods</PART>
<TOT_CNT>3121</TOT_CNT>
<TOT_AMT>33686366</TOT_AMT>
</ROW>
<ROW num="2">
<BU>111200</BU>
<PART>Equipments</PART>
<TOT_CNT>4052</TOT_CNT>
<TOT_AMT>25262637</TOT_AMT>
</ROW>
<ROW num="3">
<BU>121100</BU>
<PART>Family</PART>
<TOT_CNT>211</TOT_CNT>
<TOT_AMT>11398870</TOT_AMT>
</ROW>
<ROW num="4">
<BU>121200</BU>
<PART>Kids Ware</PART>
<TOT_CNT>266</TOT_CNT>
<TOT_AMT>10468550</TOT_AMT>
</ROW>
<ROW num="5">
<BU>151100</BU>
<PART>Ladys</PART>
<TOT_CNT>457</TOT_CNT>
<TOT_AMT>31542110</TOT_AMT>
</ROW>
<ROW num="6">
<BU>161100</BU>
<PART>Uniforms</PART>
<TOT_CNT>214</TOT_CNT>
<TOT_AMT>25224724</TOT_AMT>
</ROW>
<ROW num="7">
<BU>181100</BU>
<PART>Fashions</PART>
<TOT_CNT>499</TOT_CNT>
<TOT_AMT>43861240</TOT_AMT>
</ROW>
<ROW num="8">
<BU>191100</BU>
<PART>Sports</PART>
<TOT_CNT>405</TOT_CNT>
<TOT_AMT>31456710</TOT_AMT>
</ROW>
<ROW num="9">
<BU>321100</BU>
<PART>Culture</PART>
<TOT_CNT>17</TOT_CNT>
<TOT_AMT>32500</TOT_AMT>
</ROW>
</ROWSET>
댓글 없음:
댓글 쓰기