DB2勉強する : XML型をサクっと試してみる

XML型としてテーブルを作る

特に難しいことはなく、XMLと指定すればいいだけ。

create table CATALOG ( 
  CATALOG_ID BIGINT not null
  , CATALOG_NAME VARCHAR (128)
  , DOCUMENT XML
  , UPDATE_DATE DATE
  , primary key (CATALOG_ID)
);

XMLデータ全体を挿入・更新(insertとupdate)

手っ取り早いのは、XMLを文字列として書き込めばいい。

insert 
into CATALOG 
values ( 
  1
  , 'catalog_name'
  , '<customerinfo cid="1000">
   <name>Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
   <phone type="work">416-555-1358</phone>
</customerinfo>'
  , current timestamp
)

UPDATEも同様に文字列として更新させる。

update CATALOG 
set
  DOCUMENT = '<customerinfo cid="1000">
   <name>Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
   <phone type="work">416-555-1358</phone>
</customerinfo>'
where
  CATALOG_ID = 1

プログラムから挿入する場合は、XML型で入るようなAPIが準備されていると思いますので、わざわざ文字列にしなくてもいいかもしれません。

さて、以下のようなデータを挿入しました。次からはxmlquery関数を使って、XMLの一部を修正したいと思います。

<?xml version="1.0" encoding="UTF-8"?>
<customerinfo cid="1000">
   <name>Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
   <phone type="work">416-555-1358</phone>
</customerinfo>

XMLの要素の修正(xmlquery)

XMLの一部のデータを修正する場合は、xmlquery関数を使用し、FLOWERと呼ばれる形式で記述します。

どちらかというとプログラミング言語っぽく、習うより慣れろみたいな書き方なので、深く考えずに書いていくのがいいと思います。

要素、属性値の更新「do replace」

XMLデータの３か所を修正しています。それぞれ「要素の中身を更新」「属性値の更新」「要素自体の置き換え」とやっています。

update CATALOG 
set
  DOCUMENT = xmlquery( 
    'copy $new := $DOCUMENT 
                modify (
                    do replace value of $new/customerinfo/name with $name,
                    do replace value of $new/customerinfo/addr/@country with $country,
                    do replace $new/customerinfo/addr/pcode-zip with $zip
                )
                return $new'
                passing 
                    cast('Mike Tyson' as varchar(10)) as "name",
                    cast('America' as varchar(10)) as "country",
                    xmlparse(document '<pcode-zip2>123-456</pcode-zip2>') as "zip"
  ) 
  , UPDATE_DATE = current date 
where
  CATALOG_ID = 1

結果は以下

<?xml version="1.0" encoding="UTF-8"?>
<customerinfo cid="1000">
   <name>Mike Tyson</name>
   <addr country="America">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip2>123-456</pcode-zip2>
   </addr>
   <phone type="work">416-555-1358</phone>
</customerinfo>

要素、属性の挿入「do insert」

「要素を末尾に追加」「要素を先頭に追加」「指定した位置に追加」「属性の追加」となっております。

update CATALOG 
set
  DOCUMENT = xmlquery( 
    'copy $new := $DOCUMENT 
                modify (
                    do insert $gmail into $new/customerinfo,
                    do insert $nickname as first into $new/customerinfo,
                    do insert $phone after $new/customerinfo/phone[1],
                    do insert attribute userId {$userId} into $new/customerinfo/name
                )
                return $new'
                passing 
                    xmlparse(document '<mail type="gmail">hoge@gmail.co.jp</mail>') as "gmail",
                    xmlparse(document '<nickname>Tyson</nickname>') as "nickname",
                    xmlparse(document '<phone>1235689</phone>') as "phone",
                    cast('123' as varchar(10)) as "userId"

  ) 
  , UPDATE_DATE = current date 
where
  CATALOG_ID = 1

<?xml version="1.0" encoding="UTF-8"?>
<customerinfo cid="1000">
   <nickname>Tyson</nickname>
   <name userId="123">Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
   <phone type="work">416-555-1358</phone>
   <phone>1235689</phone>
   <mail type="gmail">hoge@gmail.co.jp</mail>
</customerinfo>

要素が存在していれば更新、なければ挿入

「要素の更新、なければ挿入」です。属性に対しても存在チェックで挿入・更新可能。

update CATALOG 
set
  DOCUMENT = xmlquery( 
    'copy $new := $DOCUMENT 
                modify (
                    if($new/customerinfo/phone) then
                        do replace value of $new/customerinfo/phone with $phone
                    else
                        do insert <phone>{fn:data($phone)}</phone> into $new/customerinfo,

                    if($new/customerinfo/phone/@type) then
                        do replace value of $new/customerinfo/phone/@type with $type
                    else
                        do insert attribute type {$type} into $new/customerinfo/phone

                )
                return $new'
                passing 
                    cast('000-0000-0000' as varchar(20)) as "phone",
                    cast('private' as varchar(20)) as "type"

  ) 
  , UPDATE_DATE = current date 
where
  CATALOG_ID = 1

<?xml version="1.0" encoding="UTF-8"?>
<customerinfo cid="1000">
   <name>Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
   <phone type="private">000-0000-0000</phone>
</customerinfo>

要素、属性の削除「do delete」

update
    CATALOG 
set
    DOCUMENT = xmlquery('copy $new := $DOCUMENT 
                modify (
                    do delete $new/customerinfo/phone,
                    do delete $new/customerinfo/@cid
                )
                return $new '),
    UPDATE_DATE = current date
where
  CATALOG_ID = 1

<?xml version="1.0" encoding="UTF-8"?>
<customerinfo>
   <name>Kathy Smith</name>
   <addr country="Canada">
      <street>5 Rosewood</street>
      <city>Toronto</city>
      <prov-state>Ontario</prov-state>
      <pcode-zip>M6W 1E6</pcode-zip>
   </addr>
</customerinfo>

名前
	情報を記憶評価顔星

DB2勉強する

XML型をサクっと試してみる