SQLの実行結果をXML化して出力する

oracle

検証環境

出力対象表サンプル

CREATE TABLE PRODUCT (
    id NUMBER,
    name VARCHAR2(100),
    price NUMBER
);

INSERT INTO PRODUCT VALUES(1, 'Catalyst 2940', 1000000);
INSERT INTO PRODUCT VALUES(2, 'SUNTORY 天然水', 2000000);
INSERT INTO PRODUCT VALUES(3, 'MacBookPro', 3000000);
INSERT INTO PRODUCT VALUES(4, 'COMME des GARCONS', 4000000);
COMMIT;

コードサンプル

マルチバイト文字を含むCLOBを出力することのほうが、XML化より難しいとはこれいかに・・・

DECLARE
    xmldoc CLOB;
    lenchr NUMBER;
    lenbin NUMBER;
    offset NUMBER;
    buffer VARCHAR2(32767);
    amount NUMBER;
BEGIN
    --XML化自体はDBMS_XMLGENのGETXMLプロシージャを使うと容易にできてしまう。
    xmldoc := DBMS_XMLGEN.GETXML('SELECT * FROM PRODUCT ORDER BY id');

    --CLOBを一発で出力できないため、bufferに小分けに格納して出力する。
    --DBMS_LOBのGETLENGTHは文字数を返すため、マルチバイト文字を含む場合はバイト数と一致しない。
    lenchr := DBMS_LOB.GETLENGTH(xmldoc);
    offset := 1;
    lenbin := 0;
    WHILE lenchr >= offset
    LOOP
        --amountにはbufferに格納する文字数を指定する。
        --バイト数ではないため、3バイト/文字の日本語が大半の場合を想定して、
        --バッファサイズ32767に対してamountは1/3以下の10000とする。
        amount := 10000;
        DBMS_LOB.READ(xmldoc,amount,offset,buffer);
        DBMS_OUTPUT.PUT(buffer);
        lenbin := lenbin + LENGTHB(buffer);
        offset := offset + amount;
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;

    --文字数とバイト数の乖離を確認する。
    DBMS_OUTPUT.PUT_LINE('lenchr: '||lenchr || ' lenbin: '||lenbin);

    --CLOBを開放する。
    DBMS_LOB.FREETEMPORARY(xmldoc);
END;
/

出力結果サンプル

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <NAME>Catalyst 2940</NAME>
  <PRICE>1000000</PRICE>
 </ROW>
 <ROW>
  <ID>2</ID>
  <NAME>SUNTORY 天然水</NAME>
  <PRICE>2000000</PRICE>
 </ROW>
 <ROW>
  <ID>3</ID>
  <NAME>MacBookPro</NAME>
  <PRICE>3000000</PRICE>
 </ROW>
 <ROW>
  <ID>4</ID>
  <NAME>COMME des GARCONS</NAME>
  <PRICE>4000000</PRICE>
 </ROW>
</ROWSET>

lenchr: 368 lenbin: 374

ユーティリティ化

CLOBのバイト長を返す
CREATE FUNCTION CLOB_GETLENGTHB( lob IN CLOB ) RETURN NUMBER AS
    lenchr NUMBER;
    lenbin NUMBER;
    offset NUMBER;
    buffer VARCHAR2(32767);
    amount NUMBER;
BEGIN
    lenchr := DBMS_LOB.GETLENGTH(lob);
    offset := 1;
    lenbin := 0;
    WHILE lenchr >= offset
    LOOP
        amount := 10000;
        DBMS_LOB.READ(lob,amount,offset,buffer);
        lenbin := lenbin + LENGTHB(buffer);
        offset := offset + amount;
    END LOOP;
    RETURN lenbin;
END;
/
CLOBをDBMS_OUTPUTで出力する
CREATE PROCEDURE CLOB_PUT_LINE( lob IN CLOB ) AS
    lenchr NUMBER;
    offset NUMBER;
    buffer VARCHAR2(32767);
    amount NUMBER;
BEGIN
    lenchr := DBMS_LOB.GETLENGTH(lob);
    offset := 1;
    WHILE lenchr >= offset
    LOOP
        amount := 10000;
        DBMS_LOB.READ(lob,amount,offset,buffer);
        DBMS_OUTPUT.PUT(buffer);
        offset := offset + amount;
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
END;
/
ユーティリティ化した後のXML化処理
DECLARE
    xmldoc CLOB;
BEGIN
    xmldoc := DBMS_XMLGEN.GETXML('SELECT * FROM PRODUCT ORDER BY id');
    CLOB_PUT_LINE(xmldoc);
    DBMS_LOB.FREETEMPORARY(xmldoc);
END;
/

文字列でクエリを渡したくない、渡せない場合

DECLARE
    product_cur SYS_REFCURSOR;
    xmldoc CLOB;
    ctx DBMS_XMLGEN.ctxHandle;
BEGIN
    --カーソルを作成。
    OPEN product_cur FOR SELECT * FROM PRODUCT ORDER BY id;

    --カーソルを渡してXML化する。
    ctx := DBMS_XMLGEN.NEWCONTEXT(product_cur);
    xmldoc := DBMS_XMLGEN.GETXML(ctx);

    CLOSE product_cur;
    DBMS_XMLGEN.CLOSECONTEXT(ctx);

    CLOB_PUT_LINE(xmldoc);

    DBMS_LOB.FREETEMPORARY(xmldoc);
END;
/

1:Nの関係を出力する

サンプルデータ追加
CREATE TABLE SELL (
    id NUMBER,
    sell_date DATE,
    amount NUMBER
);

INSERT INTO SELL VALUES(2, SYSDATE-1, 10);
INSERT INTO SELL VALUES(2, SYSDATE-2, 10);
INSERT INTO SELL VALUES(2, SYSDATE-3, 10);
INSERT INTO SELL VALUES(3, SYSDATE-1, 1);
INSERT INTO SELL VALUES(4, SYSDATE, 1);
INSERT INTO SELL VALUES(4, SYSDATE-1, 1);
COMMIT;
PRODUCT×SELLを出力
DECLARE
    product_cur SYS_REFCURSOR;
    xmldoc CLOB;
    ctx DBMS_XMLGEN.ctxHandle;
BEGIN
    --カーソルを作成。
    OPEN product_cur FOR SELECT P.ID, P.NAME, P.PRICE, CURSOR(SELECT * FROM SELL S WHERE S.ID = P.ID) SELL_HIST FROM PRODUCT P ORDER BY id DESC;

    --カーソルを渡してXML化する。
    ctx := DBMS_XMLGEN.NEWCONTEXT(product_cur);
    DBMS_XMLGEN.SETROWSETTAG(ctx, 'PRODUCTS');
    DBMS_XMLGEN.SETROWTAG(ctx, 'PRODUCT');
    xmldoc := DBMS_XMLGEN.GETXML(ctx);

    CLOSE product_cur;
    DBMS_XMLGEN.CLOSECONTEXT(ctx);

    CLOB_PUT_LINE(xmldoc);

    DBMS_LOB.FREETEMPORARY(xmldoc);
END;
/
出力結果
<?xml version="1.0"?>
<PRODUCTS>
 <PRODUCT>
  <ID>4</ID>
  <NAME>COMME des GARCONS</NAME>
  <PRICE>4000000</PRICE>
  <SELL_HIST>
   <SELL_HIST_ROW>
    <ID>4</ID>
    <SELL_DATE>14-07-20</SELL_DATE>
    <AMOUNT>1</AMOUNT>
   </SELL_HIST_ROW>
   <SELL_HIST_ROW>
    <ID>4</ID>
    <SELL_DATE>14-07-19</SELL_DATE>
    <AMOUNT>1</AMOUNT>
   </SELL_HIST_ROW>
  </SELL_HIST>
 </PRODUCT>
 <PRODUCT>
  <ID>3</ID>
  <NAME>MacBookPro</NAME>
  <PRICE>3000000</PRICE>
  <SELL_HIST>
   <SELL_HIST_ROW>
    <ID>3</ID>
    <SELL_DATE>14-07-19</SELL_DATE>
    <AMOUNT>1</AMOUNT>
   </SELL_HIST_ROW>
  </SELL_HIST>
 </PRODUCT>
 <PRODUCT>
  <ID>2</ID>
  <NAME>SUNTORY 天然水</NAME>
  <PRICE>2000000</PRICE>
  <SELL_HIST>
   <SELL_HIST_ROW>
    <ID>2</ID>
    <SELL_DATE>14-07-19</SELL_DATE>
    <AMOUNT>10</AMOUNT>
   </SELL_HIST_ROW>
   <SELL_HIST_ROW>
    <ID>2</ID>
    <SELL_DATE>14-07-18</SELL_DATE>
    <AMOUNT>10</AMOUNT>
   </SELL_HIST_ROW>
   <SELL_HIST_ROW>
    <ID>2</ID>
    <SELL_DATE>14-07-17</SELL_DATE>
    <AMOUNT>10</AMOUNT>
   </SELL_HIST_ROW>
  </SELL_HIST>
 </PRODUCT>
 <PRODUCT>
  <ID>1</ID>
  <NAME>Catalyst 2940</NAME>
  <PRICE>1000000</PRICE>
  <SELL_HIST>
  </SELL_HIST>
 </PRODUCT>
</PRODUCTS>