PR

SQL ServerでのXML活用ガイド：柔軟なデータ管理のためのXML機能を徹底解説

データベースにおけるXMLの利用は、複雑なデータ構造や柔軟なデータ管理を可能にします。

この記事では、SQL ServerでのXMLデータの操作方法と、番外編として意外な活用例について紹介します。

データベース上でXMLデータを扱えるようになると、更に高度な業務もこなせるようになりますので、是非チャレンジしてみて下さい！

スポンサーリンク

XMLとは

XML（eXtensible Markup Language）は、テキスト形式で様々な形のデータを表現することができる標準的なマークアップ言語です。

テキスト形式であるため可読性が高く、また柔軟性が高いため、様々なデータの交換に活用されています。

XMLはタグ（<タグ名>…</タグ名> ）でデータを囲むことで、データの階層構造を表現します。属性はタグの中に記述（<タグ名　属性名=”属性”> ）され、追加の情報を提供します。

具体例として家電のスペックをXMLデータで表現すると以下のようになります。

エアコンの例

<製品>
    <製品種類>エアコン</製品種類>
    <冷房能力 単位="kW">2.2</冷房能力>
    <冷房消費電力 単位="W">580</冷房消費電力>
    <サイズ>
        <室内機サイズ 単位="mm">幅798x高さ250x奥行255</室内機サイズ>
        <室外機サイズ 単位="mm">幅675x高さ550x奥行284</室外機サイズ>
    </サイズ>
    <室内機重量 単位="kg">8.5</室内機重量>
    <室外機重量 単位="kg">20</室外機重量>
</製品>

電子レンジの例

<製品>
    <製品種類>電子レンジ</製品種類>
    <庫内容量 単位="L">26</庫内容量>
    <レンジ機能>
        <最大レンジ出力 単位="W">1000</最大レンジ出力>
    </レンジ機能>
    <オーブン機能>
        <オーブン最高温度 単位="℃">250 ℃</オーブン最高温度>
        <オーブン最低温度 単位="℃">100 ℃</オーブン最低温度>
    </オーブン機能>
    <サイズ 単位="mm">幅500x高さ347x奥行400</サイズ>
    <質量 単位="kg">14.1 kg</質量>
    <庫内寸法 単位="mm">幅319x高さ215x奥行350</庫内寸法>
</製品>

このように製品の種類ごとにスペックの項目は大きく異なりますが、XMLで表現することで同じテキスト形式で表現することが可能です。

Microsoft Office のファイルも XML ファイルで構成されている

Microsoft Office 2007より前のファイルはバイナリ形式で、ファイルの内容を読み取ることが難しく、
その他のソフトウェアとの互換性が低いという課題がありました。Microsoft はこれを改善するため、
Microsoft Office 2007 以降は XML ベースの Open XML 形式のファイルを導入しています。

例えば Book1.xlsx というファイルがあった場合、拡張子を変更して Book1.zip というファイル名にして
解凍すると図のようにフォルダ構成と共に いくつかのXMLファイルが展開されます。

これらの XMLファイルはテキストファイルなので開いて中身を読むことができます。

あれだけ複雑な Microsoft Office のファイル情報を整理して記録できるのですから、XMLがいかに柔軟性に富んだデータ形式であるかが分かります。

スポンサーリンク

リレーショナルデータベースでXMLを扱うことについて

リレーショナルデータベースの特徴とは

そもそもリレーショナルデータベースは、データの形式を厳格に定めて同じ形式のデータを大量に格納するものです。

テーブル列（フィールド）の名称、データ型を固定することで、大量なデータを高速で処理したり、データを組み合わせて複雑な処理を行ったりすることができます。

リレーショナルデータベースのメリットとデメリット

つまり、このデータの形式を固定することがリレーショナルデータベースのメリットを引き出している訳です。

しかし、このメリットは逆にデメリットも生み出します。それがデータ形式の柔軟性の欠如です。通常のリレーショナルデータベースのテーブルには少しでも形の異なるデータは格納することが出来ません。

ですので、データ形が少しずつ異なる大量のデータがあった場合は、全く対処できなくなってしまいます。

XMLデータ活用によるイイとこ取り

この課題を解消することができるのが XMLデータ の採用 なのです。XMLは一つの長い文字列で多様な形式のデータを表現することが可能です。

SQL Server ではテーブル列（フィールド）に この XMLデータ形式 を設定することが可能となっています。

これにより、SQL Server はリレーショナルデータベースの特徴である固定データ形式による高速処理と、XMLによる柔軟なデータ取り扱いの両方の機能を併せ持つことが出来ているのです。

XMLデータのデメリット

しかし、世の中旨い話ばかりはありません。XMLデータは多様な形式のデータを表現できますが、取り扱い時に文字列を解析する必要がありますので、固定データ形式と比べると処理速度は低下してしまいます。

何でも XMLデータ形式でデータを格納してしまうと、リレーショナルデータベースのメリットを損なう結果になってしまいます。

従って、ユーザーは固定データ形式とXML形式のメリット、デメリットを良く理解して最適なデータベース設計を行う必要があります。

XML活用ケース

例えば、様々な製品情報を一つのフィールドに格納する場合や、ログデータを階層的に整理する際にXMLが有効です。実際の業務での利用例を紹介し、その利便性をお伝えします。

スポンサーリンク

SQL Server での XMLデータ操作

データベースの運用はシンプルで、テーブルを作成した後は 読み、書き、更新、削除 のいわゆる CRUD (Create, Read, Update, Delete) の4つの操作が全てです。

ですので、この章では XMLデータを格納できるテーブルの作成、XMLデータの CRUD 操作 について紹介します。

テーブルにXMLデータ列を作る

まずは、SQL ServerでXMLデータを格納できるテーブルの作成方法をご紹介します。

これには以下のような SQLコマンド を実行します。

データ型に xml を指定するだけです。これはとても簡単ですね。

CREATE TABLE 製品 (
    製品ID int PRIMARY KEY,
    製品名 varchar(100),
    製品仕様 xml
);

XMLデータの追加

次に、XMLデータをテーブルに挿入する方法を見てみましょう。SQL Serverでは、INSERT文を使ってXMLデータを追加できます。具体的なコード例を使って解説します。

これも理解し易いと思います。その他のデータと同じように XMLの文字列をデータとして入力しています。

INSERT INTO 製品 (製品ID, 製品名, 製品仕様)
VALUES (1, '黒クマさん', 
'<製品>
    <製品種類>エアコン</製品種類>
    <冷房能力 単位="kW">2.2</冷房能力>
    <冷房消費電力 単位="W">580</冷房消費電力>
    <サイズ>
        <室内機サイズ 単位="mm">幅798x高さ250x奥行255</室内機サイズ>
        <室外機サイズ 単位="mm">幅675x高さ550x奥行284</室外機サイズ>
    </サイズ>
    <室内機重量 単位="kg">8.5</室内機重量>
    <室外機重量 単位="kg">20</室外機重量>
</製品>');

XMLデータの読み出し（Read）

XMLデータをクエリして特定のノードや属性を取得する方法を紹介します。

SQL Serverのvalue()関数やquery()メソッドを使用して、必要なデータを抽出する方法を解説します。

SELECT 製品仕様.value('(/製品/サイズ)[1]', 'varchar(200)')
FROM 製品;

XMLデータの更新（Update）

XMLデータの特定部分を更新する方法について紹介します。

例えば、 XMLデータの中の製品のサイズを変更するケースに場合のSQLクエリは以下のようになります。

UPDATE 製品
SET 製品仕様.modify('replace value of (/製品/冷房能力/text())[1] with 2.5')
WHERE 製品ID = 1;

XML データのノードの削除（Delete）

XMLデータの不要なノードを削除する方法を説明します。

例えば、製品のカラーノードを削除する場合は以下のようなSQLクエリになります。

UPDATE 製品
SET 製品仕様.modify('delete /製品/冷房能力')
WHERE 製品ID = 1;
スポンサーリンク

通常テーブルからのXMLデータ出力 （FOR XML）

前の章では XMLデータ の読み書きの方法を紹介しました。

この章では通常のテーブルから読み出したデータを XMLデータ形式にして，出力する方法をご紹介します。

それが FOR XML というコマンドです。

FOR XMLの基本

SQL Serverでは、FOR XMLというコマンドを使用して通常のテーブルのデータをXML形式で出力できます。

通常の SELECTコマンド の後に FOR XML をつけて使います。

FOR XMLには、RAW, AUTO, PATH, EXPLICIT の４つのモード があります。主に出力される XMLの階層構造に違いがあります。

サンプルデータとして以下のようなデータがある前提で、それぞれのモードの SQLクエリ と 出力の具体例を見ていきましょう。

RAWモード

RAWモードは最もシンプルなモードです。テーブルの１行が階層を持つことなく属性やタグで出力されます。

AUTOモード

AUTOモードはテーブルの結合の関係性に合わせて階層構造を自動で作成して出力するモードです。

PATHモード

PATHモードはユーザが各出力の階層位置や属性などを指定することができ、柔軟な出力が可能です。

EXPLICITモード

EXPLICITモードは ユニバーサルテーブル という、あるルールに基づいた出力をするSQLクエリを記述することで、厳密な XML を出力できるモードになります。

ユニバーサルテーブル はXMLのデータ構造を別の形で表現しているので、記述量が非常に多くなってしまうので、
プログラミングのコストが過大になる懸念があります。

厳密な XMLデータを作成せざるを得ない状況の時のみ使用することをオススメします。

詳細は Microsoftのリファレンス をご確認下さい。

スポンサーリンク

番外編：XML機能の意外な活用術

文字列の分割・配列化

データが区切り文字を含む文字列で、ここから3番目の要素を取り出したいという場合、SQLでどのように
処理すれば良いでしょうか？

CHARINDEX関数 を使って長い関数式を作成するとか、いくつか方法がありますが、XMLの文字列解析機能を
使うとスマートに解決できます。

例えば、図のようにフォルダ内のパス情報が含まれるテーブルがあった時、ここから “￥” マーク で区切られた
6番目の要素を取り出したいとします。その場合は、下記のようなSQLクエリで実現できます。

文字列分割・抽出クエリ

with toXML as (
    select cast('<x>' + replace(Path, '\', '</x><x>') + '</x>' as xml) as xmlfld
    from PathList
)

select xmlfld.value('/x[6]', 'varchar(max)') as 名称
from toXML

解説

  1. WITH句 でCTE（共通テーブル）を作成します。
  2. 文字列の区切り文字を XMLタグ（ \\ ）に変換、前後にも XMLタグ をつけて XMLデータに変換する
  3. CAST関数で文字列を XMLデータに変換
  4. 共通テーブル（toXML）から XMLの \ 要素 の６番目の要素 を SELECT。データ型は varchar(max) を指定。

出力結果

上記のSQLクエリの実行結果は図のようになります。やりたかった処理ができていることが分かります。

スポンサーリンク

まとめ

SQL Server は強力なリレーショナルデータベースです。データ形式を厳格に定めて高速処理を実現する一方、XMLデータも扱うことで高度に柔軟なデータの取り扱いも可能です。

従来のリレーショナルデータベースとXMLデータの特徴を良く理解して使いこなすことで更に高度なデータの運用が可能となります。

この機会にこの記事で得た知識を活用してより高度な業務にチャレンジしてみては如何でしょうか？

タイトルとURLをコピーしました