リストで管理されたidを別々カラムとして出力する(XMLTABLE)

皆さんこんにちは。

DBから取ってこようとしたidがリストで登録されていて１つ１つのidとして取って来れない、、と感じたことはありませんか？
その場合、where句を使用してidを絞ったりすることも難しくなります。

今回はそのような状況で１つ１つを別々のカラムで出力する方法をご紹介させていただきます。

この記事は、株式会社アクティブコアAdvent Calendar 18日目の記事になります(遅れたけど)。

はじめに

使用するDBは「DB2」です。
リストに登録される最大要素数、今回は「5」として実施します。

登録されてているデータが「1」「1,23,43,12,22」「12,3,45」
の場合以下のような形で出力されることを目指します。

ID1_1 ID1_2 ID1_3 ID1_4 ID1_5
----- ----- ----- ----- -----
1     -     -     -     -    
1     23    43    12    22   
12    3     45    -     -

SUBSTRとLOCATEで試してみた

テーブル作ってデータ入れるのが億劫だったのでWITH句の部分をテーブルと仮定して以下のSQLを実行
実行したSQL

WITH temporaryName AS (
    SELECT 
    '1' as id_list1, 
    '1,23,43,12,22' as id_list2,
    '12,3,45' as id_list3
FROM sysibm.sysdummy1
)
SELECT 
    SUBSTR(id_list1, 1, LOCATE(',', id_list1) - 1) as id1_1,
    SUBSTR(id_list2, 1, LOCATE(',', id_list2) - 1) as id2_1,
    SUBSTR(id_list3, 1, LOCATE(',', id_list3) - 1) as id3_1
FROM temporaryName;

出力結果

ID1_1 ID2_1         ID3_1
----- ------------- -------
SQL0138N  スカラー関数の数値引数が範囲外であるため、ステ
ートメントは実行されませんでした。  SQLSTATE=22011
db2 =>

idが１つしか登録されていない場合はカンマがないため、DB2におこられてしまいました、、

力技でやってみた

実行したSQL

WITH temporaryName AS (
    SELECT 
    '1' as id_list1, 
    '1,23,43,12,22' as id_list2,
    '12,3,45' as id_list3
FROM sysibm.sysdummy1
)
SELECT 
    SUBSTR(id_list2, 1, LOCATE(',', id_list2) - 1) as id2_1,
    SUBSTR(id_list2, LOCATE(',', id_list2) + 1, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) - LOCATE(',', id_list2) - 1) AS id2_2,
    SUBSTR(id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1) - LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) - 1) AS id2_3,
    SUBSTR(id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1) + 1, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1) + 1) - LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1) - 1) AS id2_4,
    SUBSTR(id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2, LOCATE(',', id_list2) + 1) + 1) + 1) + 1) AS id2_5,
    SUBSTR(id_list3, 1, LOCATE(',', id_list3) - 1) as id3_1,
    SUBSTR(id_list3, LOCATE(',', id_list3) + 1, LOCATE(',', id_list3, LOCATE(',', id_list3) + 1) - LOCATE(',', id_list3) - 1) AS id3_2,
    SUBSTR(id_list3, LOCATE(',', id_list3, LOCATE(',', id_list3) + 1) + 1) AS id3_3
FROM temporaryName;

出力結果

ID2_1         ID2_2         ID2_3         ID2_4         ID2_5         ID3_1   ID3_2   ID3_3
------------- ------------- ------------- ------------- ------------- ------- ------- -------
1             23            43            12            22            12      3       45

  1 レコードが選択されました。

「ID2_X」「ID3_X」は別々のレコードであるという体なので出力結果は悪くないですが、先程のエラーが出ないようにリストの要素の数に合わせて、出力内容調整しているので実用性で言うと皆無でした。（見にくいのもありますが）

エラーが出ずに、登録されているいidの個数に左右されない方法を調べたところ「XMLTABLE」というものがあるらしい、、

XMLTABLEを使ってやってみた

実行したSQL

WITH temporaryName AS (
    SELECT 
    '1' as id_list1, 
    '1,23,43,12,22' as id_list2,
    '12,3,45' as id_list3
FROM sysibm.sysdummy1
)
SELECT 
    id_list1.id1_1,
    id_list1.id1_2,
    id_list1.id1_3,
    id_list1.id1_4,
    id_list1.id1_5,
    id_list2.id2_1,
    id_list2.id2_2,
    id_list2.id2_3,
    id_list2.id2_4,
    id_list2.id2_5,
    id_list3.id3_1,
    id_list3.id3_2,
    id_list3.id3_3,
    id_list3.id3_4,
    id_list3.id3_5
FROM temporaryName,
    XMLTABLE('$D/d' PASSING XMLPARSE(DOCUMENT '<d><e>' || REPLACE(temporaryName.id_list1, ',', '</e><e>') || '</e></d>') AS "D" COLUMNS id1_1 VARCHAR(2) PATH 'e[1]', id1_2 VARCHAR(2) PATH 'e[2]', id1_3 VARCHAR(2) PATH 'e[3]', id1_4 VARCHAR(2) PATH 'e[4]', id1_5 VARCHAR(2) PATH 'e[5]') AS id_list1,
    XMLTABLE('$D/d' PASSING XMLPARSE(DOCUMENT '<d><e>' || REPLACE(temporaryName.id_list2, ',', '</e><e>') || '</e></d>') AS "D" COLUMNS id2_1 VARCHAR(2) PATH 'e[1]', id2_2 VARCHAR(2) PATH 'e[2]', id2_3 VARCHAR(2) PATH 'e[3]', id2_4 VARCHAR(2) PATH 'e[4]', id2_5 VARCHAR(2) PATH 'e[5]') AS id_list2,
    XMLTABLE('$D/d' PASSING XMLPARSE(DOCUMENT '<d><e>' || REPLACE(temporaryName.id_list3, ',', '</e><e>') || '</e></d>') AS "D" COLUMNS id3_1 VARCHAR(2) PATH 'e[1]', id3_2 VARCHAR(2) PATH 'e[2]', id3_3 VARCHAR(2) PATH 'e[3]', id3_4 VARCHAR(2) PATH 'e[4]', id3_5 VARCHAR(2) PATH 'e[5]') AS id_list3
;

出力結果

ID1_1 ID1_2 ID1_3 ID1_4 ID1_5 ID2_1 ID2_2 ID2_3 ID2_4 ID2_5 ID3_1 ID3_2 ID3_3 ID3_4 ID3_5
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1     -     -     -     -     1     23    43    12    22    12    3     45    -     -

  1 レコードが選択されました。

「ID1_X」「ID2_X」「ID3_X」を別々のレコードとしてみるので、元々表示したい形式で表示できているし、各レコードの要素数に合わせて記載内容を変える必要もないので実用性も高そうです！！

XMLTABLEがどういうものか調べてみた

今回使用したXMLTABLEを分かる範囲で説明します。（間違っているかもですが、、）

以下のような形で指定し、XMLPARSEでXMLドキュメントに変換します

XMLPARSE(DOCUMENT 'ドキュメントの頭を設定' || REPLACE(カラムを設定, '置換対象の文字列', '置換後の文字列') || 'ドキュメントのケツを設定')

例として

XMLPARSE(DOCUMENT '<d><e>' || REPLACE(temporaryName.id_list2, ',', '</e><e>') || '</e></d>')

の場合、id_list2（1,23,43,12,22）が以下のように変換されます

<d>
<e>1</e>　※要素1
<e>23</e> ※要素2
<e>43</e> ※要素3
<e>12</e> ※要素4
<e>22</e> ※要素5
</d>

※見やすいように整形しています

後は以下のように記載すると各要素を個別に取り出し、任意のカラム名を付ければ出力できるようになりました。
存在しない要素はNULLとして出力されるので、listにidが１つだけでも問題なく動作します。

XMLTABLE('$D/d' PASSING XMLPARSE(DOCUMENT '<d><e>' || REPLACE(temporaryName.id_list1, ',', '</e><e>') || '</e></d>') AS "D" COLUMNS 任意のカラム名 カラム長 PATH 'どのタグの[何番目の要素]',・・・)

作成してみて

今回はリストで登録されたidを要素に分けて取り出すというのを試みました。
結果SUBSTRとLOCATEではなく、初めて使用するXMLTABLEで実施する形となりました。
使いながら覚えた感じなので、正直まだわからないことも多いですが大まかに使用法を理解できたのでアウトプットしてみて良かったです。
また、今回はリストのid数が5までで実施しましたが、6,7,8・・・と増やせるので汎用性も高そうです

最後に

XMLTABLEをよくわからないまま使用していたので記事作成に思った以上に時間がかかってしまいました。
他にもっと良い方法あれば教えてください！