「Excel Tips」ではExcelを使用していて気付いたことを取り上げて行きます。
今回はExcelでXMLファイル読み込む(XMLデータのインポート)際のXMLスキーマファイル(.xsd)の働きについての話題の第1回目になります。
XMLは仕様が定められてから20年近くたちあまり目新しい話題があるわけではありませんが、今回改めてExcelで読み込んでみて気付いた点を実際にExcelで動かせるサンプルを使いながらご紹介したく存じます。
なおExcelでのXMLファイル書き込みにつきましては、ほぼほぼ書き込みはできないのですが、その実例を別な記事でご紹介する予定です。
Excel 2016バージョン2107(ビルド 14228.20250)を使用しています。
XMLスキーマファイル(.xsd)を使ってXMLデータをインポートするには
ExcelでXMファイルを読み込むにはいくつかのやり方が存在します。
詳しい操作は省略致しますが、例えばメニュー「ファイル」から「開く」でファイルの拡張子を「XMLファイル(*.xml)」にしてもXMLファイルを読み込む事ができます。
「XML ソース」作業ウィンドウの「XMLの対応付け…」(「XMLマップ」)を使う
今回はXSDファイルの役割をテーマにしているので少し面倒ではありますが「開発」タブの「XML」グループで「ソース」をクリックして「XML ソース」作業ウィンドウの中の「XMLの対応付け…」(下記Microsoft Officeサポートの「EXCEL の XML の概要」ページでは「XMLマップ」と書かれている)ボタンを使用する事にします。
開発タブの表示方法につきましてはMicrosoft Officeサポートの下記のページをご参照ください。
「XMLスキーマファイル(*.xsd)」ではなく「XMLファイル(*.xml)」を選択
先の章の「XMLの対応付け…」ボタンをクリックすると下左図のような「XMLの対応付け」ウインドウが開きます。
ここでカーソルが当たっている「追加」ボタンをクリックすると下右図のような「XMLソースの選択」ウインドウが開きます。
この時に指定できるファイルの拡張子は「XMLファイル(*.xml)」か「XMLスキーマファイル(*.xsd)」のいずれかになります。
前章でご紹介したMicrosoft Officeサポートの「EXCEL の XML の概要」の中の「Excel で XML データを使う基本的なプロセス」の章では、「XMLスキーマファイル(*.xsd)」を追加するところから始まっています。
ただし今回は「XMLファイル(*.xml)」を選択する事にしています。その理由は下記になります。
- 複数のスキーマファイル(*.xsd)が設定されているXMLファイルの場合、どのスキーマ―ファイルを読み込めば良いのか判らない。
- 複数のグローバル宣言された要素が含まれているスキーマファイル(*.xsd)の場合、Excelでは1つのグローバル宣言された要素に基づく対応付けしか作成できないので、どれを選べば良いのか判らない。
- 【2021/9/9 追記】スキーマ構造によっては、このグローバル宣言された要素判定機能が正しく動作しないケースがある事が解りました。その場合の対処方法を含めて、詳しくは第3回目の「その3」でご説明いたします。
※どちらの場合もITに慣れた人であれば「中身を見て判断する事ができる」と思いますが、ITに不慣れな人の場合はサポートが必要になる認識です。
※両者ともにXMLファイルで読み込んだ場合は対応が必要ありません。これらの実例につきましては後述いたします。
グローバル宣言とローカル宣言
詳細につきましては下記@ITの「最終回 XML Schema―型の再利用と名前空間」をご参照いただきたのですが、簡単に内容をまとめておきます。
- グローバル宣言
- ルート要素直下の子要素として宣言されている「要素(=element」や「属性(=attribute)」や「型(=type)」の宣言
- 読み込まれているスキーマファイルも含め、どこからでも参照可能
- ローカル宣言
- ルート要素直下の子要素ではなく、ある要素の子要素として宣言されている「要素(=element」や「属性(=attribute)」や「型(=type)」の宣言
- 記述されている子要素の括りの中でのみ参照可能
『スキーマファイル(.xsd)を「参照している」XMLファイル』を選択する場合
前章で「XMLファイル(*.xml)」を選んだ場合、XMLファイルがスキーマファイル(.xsd)を「参照しているか?」、「参照していないか?」によってその後の動き方が異なります。
「参照していない」場合につきましては今回のテーマから外れるのですが、操作方法についてまとめておきます。
XMLファイルがスキーマファイル(.xsd)を「参照していない」場合の操作方法
- 選んだXMLファイルがスキーマファイル(.xsd)を「参照していない」場合、左図①のメッセージが表示されるので、「OK」ボタンをクリックします。
- 「XMLの対応付け」ウインドウに「推測されたスキーマ」が登録されます。
- 「XMLソース」ウインドウには「XMLファイルで定義されているタグに基づいてExcelが推測したスキーマが「階層リストで表示」されます。
注意すべきポイント
- XMLファイルに複数の名前空間が定義されている場合は『「ns」+連番』の接頭辞が自動附番されます。
- 当然ではありますが、XMLファイルに定義されているタグが「スキーマで定義されている要素の一部しか含まれていない場合」は、「推測できるスキーマは一部分だけ」になります。
- スキーマで定義される型や属性も「データありき」のものなので制約にはなりません。
以上が「参照していない」場合の操作方法になります。
なお「XML名前空間」につきましては、下記@ITの「XML名前空間でタグを使い分ける」をご参照いただければ幸いです。ただし約20年前の2002年の記事であるためスキーマ言語は新しいXSDではなく古いDTDで表現されていますのでお含み置きください。
XMLファイルがスキーマファイル(.xsd)を「参照している」場合の操作方法
実は「参照している」場合の操作方法は、「参照していない」場合の操作方法の①が無いだけでまったく同じ画面展開になります。
しかも③の「XMLソース」ウインドウに階層リストで表示される接頭辞も、実際にXMLファイルに指定されているものではなく『「ns」+連番』での表示となり、XMLファイルに定義されているタグが「スキーマで定義されている要素の一部しか含まれていない場合」以外では見た目に両者の違いは現れません。
XMLファイルでスキーマファイル(.xsd)を「参照させる」ためには
XMLファイルに何も指定がされてなければスキーマファイル(.xsd)を参照する事はできません。
実は「XMLスキーマファイル(*.xsd)」を先に読み込む場合は「無くても良い」設定をXMLファイルでしなければなりませんが、これは先の章の『「XMLファイル(*.xml)」を選択する理由』で上げた課題を解決するためには必要な設定になります。
具体的には、名前空間を一括して宣言している「ルート要素のタグ」の中でスキーマファイル(.xsd)の配置場所を指定する「schemaLocation属性」を指定しなければなりません。
「schemaLocation属性」は名前空間 「http://www.w3.org/2001/XMLSchema-instance」に属しているので、接頭辞は「xsi」が使われる事が多い認識です。
例えば下記のような形で指定する事になります。
<ルート要素 xmlns="名前空間ns" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="名前空間ns 名前空間My.xsd">
「schemaLocation属性」の値は、『「名前空間の名前」+半角スベース+「対応するスキーマのファイル名」』の形で指定します。
ちなみに上記の例では次のようになります。
- 名前空間の名前 → 名前空間ns
- 対応するスキーマのファイル名 → 名前空間My.xsd
- XMLファイルと名前空間My.xsdは同じフォルダーに格納されている必要があります。
追加情報
- 複数のスキーマファイル(*.xsd)が設定されているXMLファイルの場合は，『…』同士を半角スペースで区切って続ける形で指定します。
- 【例】xsi:schemaLocation=”名前空間ns1 名前空間My1.xsd 名前空間ns2 名前空間My2.xsd”
- 名前空間が定義されていないスキーマファイル(*.xsd)の場合、「noNamespaceSchemaLocation属性」を使用します。
- 「noNamespaceSchemaLocation属性」も名前空間 「http://www.w3.org/2001/XMLSchema-instance」に属しています。
- 「xsi:noNamespaceSchemaLocation=”No名前空間My.xsd”>」の形式で指定します。
XML要素をワークシートのセルに対応付ける
XMLファイルからデータを読み込むためには、「XMLの対応付け…」(「XMLマップ」) に登録された スキーマ「階層リスト」からワークシートに取り込みたいXML要素を選択して、セル範囲に設定する作業を事前にして置く必要があります。
Microsoft Officeサポートでは「XML 要素を XML マップのセルに対応付ける」という解り難い日本語で訳されているのですが、このドキュメントの中に操作方法と注意事項が載っています。
XML要素が「繰り返す」場合と繰り返さない「単一」の場合で見出しの設定の仕方が異なるのですが、 今回は「繰り返す」場合で、「階層リスト」 から「① XML要素を選択 するケース」と「②すべての要素を選択するケース」の2つのパターンについて、キャプチャーを使ってポイントだけご説明いたします。
- 「階層リスト」 からXML要素を複数選ぶためには、要素を選択した後、つぎの要素を選択するためには「ctrlキー」を押しながら要素をクリックします。
- 全要素を選択する場合はルート要素を選択します。
- すべての要素を選択し終えたら、XMLファイルからデータを貼り込む「先頭のセル」をワークシート上で選択します。
- その後「階層リスト」の選択した要素の上にカーソルを合わせ、その状態でマウスの右ボタンをクリックしてショートカットメニューを表示させて「要素の対応付け(M)…」をクリックします。
- 「XML要素の対応付け」ウインドウが開き、「XML要素を対応付け場所を指定してください」のテキストボックスにはデフォルトでセル範囲が入力されているので、それで良いかを確認して「OK」ボタンをクリックします。
- 「繰り返し要素」 の場合は、セル範囲にテーブルが設定されて最初の行には「階層リスト」に表示されている接頭辞付き要素名が列ヘッダーとしてセットされます。
これでXML要素をワークシートのセルに対応付けるための操作は完了です。
ワークシートのセルにXMLファイルのデータを読み込む
前章までで読み込むための準備がすべて終わったので、実際にXMLファイルのデータをワークシートに取り込むの操作に移ります。
今回の操作方法では「XMLの対応付け…」でXMLファイルを選択しているので、取り込むためのファイルは既に指定してある状態です。
そのために「開発」タブの「XML」グループにある「データの更新」が有効になっていますので、クリックするとデータがワークシートに反映されます。
長くなりましたが、以上でデータを読み込むまでの一通りの操作説明が終わりましたので、次の章から実際に動かせるサンプル使ってXSDの働きについて確認して行きます。
検証するためのサンプルファイル
複数のスキーマファイル(*.xsd)が設定されているサンプルをGoogleで検索したところ、下記のサイトにサンプルファイルが掲載されているのが見つかりました。
東京電機大学の学生向けに書かれた講義資料のようで、講義で使うために構造を簡略化しているものと推察いたします。
ただこのままでは今回の説明に必要な要素が少し足りていなかったので、構造を参考にさせていただきながら一部変更を致しました。
| ◢ | A | B | C | D | E | F |
| 1 | discs | |||||
| 2 | disc | |||||
| 3 | title | |||||
| 4 | players | |||||
| 5 | name | |||||
| 6 | artists | |||||
| 7 | artist | |||||
| 8 | name | |||||
| 9 | type |
| 説明 |
| ←discの集合。ルート要素 |
| ←レコード or CD |
| ←タイトル |
| ←プレーヤーの集合 |
| ←グループ名(無くても可) |
| ←メンバーの集合 |
| ←メンバー |
| ←メンバー名 |
| ←担当セクション |
太字の要素はスキーマファイル(*.xsd)を分けています。
スキーマを分ける理由はいくつかある事と存じますが3つ程上げておきます。
- 要素名が重ならないようにする時
- 別々のXMLLファイルを1つにまとめた時などに発生し易い。
- スキーマファイル(*.xsd)が複雑になり、行数が多くなった時
- 別なXMLファイルで、分割したスキーマをそのまま使える時
今回は、cd.xml・cd.xsd・artist.xsdの3つのファイルがそれそれ30行ぐらいになるように加工しています。
なおスキーマの要素、属性、データ型などの説明につきましては、2004年に書かれた記事ですが下記@ITの連載「SEのためのXML Schema入門」全6回をご参照いただければ幸いです。
cd.xml
<?xml version="1.0" encoding="utf-8"?>
<cd:discs xmlns:cd="cd-ns"
xmlns:art="artist-ns"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="cd-ns cd.xsd"><!-- 1 --><!-- 2 -->
<cd:disc>
<cd:title>Pet Sounds</cd:title>
<cd:players><!-- 3 -->
<cd:artists>
<art:artist>
<art:name>Brian Wilson</art:name>
<art:type>Songwriter</art:type>
</art:artist>
</cd:artists>
</cd:players>
</cd:disc>
<cd:disc>
<cd:title>Love You</cd:title>
<cd:players>
<cd:name>The Beach Boys</cd:name>
<cd:artists>
<art:artist>
<art:name>Brian Wilson</art:name>
<art:type>Songwriter</art:type>
</art:artist>
<art:artist>
<art:name>Michael Edward Love</art:name>
<art:type>Lead vocal</art:type>
</art:artist>
</cd:artists>
</cd:players>
</cd:disc>
</cd:discs>
<!– 1 –>ルート要素
- 接頭辞
- 「名前空間」の名前
- 要素の名前
上記をすべて同じ値に設定する事でコーティングミスを減らす事はできますが、初見での可読性は悪くなる認識です。
そのため3つは別になるようなネーミングを採用しています。
<!– 2 –>
schemaLocationでcd.xsdだけをセットします。
artist.xsdはcd.xsdでimportされるので、問題ありません。
<!– 3 –>
playersの子要素にしたname要素がありませんが「無くても可」として定義しています。
cd.xsd
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns="cd-ns"
targetNamespace="cd-ns"
xmlns:art="artist-ns"
elementFormDefault="qualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><!-- 1 -->
<xsd:import namespace="artist-ns" schemaLocation="artist.xsd" />
<!--discs-->
<xsd:element name="discs" type="discsType"/>
<xsd:complexType name="discsType">
<xsd:sequence>
<xsd:element maxOccurs="unbounded" ref="disc"/>
</xsd:sequence>
</xsd:complexType>
<!--disc-->
<xsd:element name="disc" type="discType"/>
<xsd:complexType name="discType">
<xsd:sequence><!-- 2 -->
<xsd:element minOccurs="1" maxOccurs="1" ref="title"/>
<xsd:element ref="players"/>
</xsd:sequence>
</xsd:complexType>
<!--title-->
<xsd:element name="title" type="xsd:string"/>
<!--players-->
<xsd:element name="players" type="playersType"/>
<xsd:complexType name="playersType">
<xsd:sequence><!-- 3 -->
<xsd:element minOccurs="0" maxOccurs="1" name="name"
type="xsd:string"/>
<xsd:element name="artists" type="art:artistType"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<!– 1 –>schema要素
本スキーマの中にはLocal属性でartist.xsdの要素を指定している箇所があります。そのためelementFormDefault属性をqualifiedに設定しています。
import要素でartist.xsdを指定
<!– 2 –>title要素
タイトルは「discに1つ存在」するように、minOccurs=”1″ maxOccurs=”1″に設定
<!– 3 –>name要素
グループ名は「無くても可」で有っても1つになるように、 minOccurs=”0″ maxOccurs=”1″に設定
artist.xsd
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns="artist-ns"
targetNamespace="artist-ns"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<!--name-->
<xsd:element name="name">
<xsd:simpleType><!-- 1 -->
<xsd:restriction base="xsd:string">
<xsd:maxLength value="18" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<!--type-->
<xsd:element name="type" type="xsd:string"/>
<!--artist-->
<xsd:element name="artist">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="name"/>
<xsd:element ref="type"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="artistType">
<xsd:sequence><!-- 2 -->
<xsd:element maxOccurs="1" ref="artist"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
スキーマの動きを確認するために次の制約を設定しています。
制約値はサンプルのXMLデータでも対象になるように非現実的な値にしていますので、お含み置きください。
<!– 1 –>name要素
メンバーの名前の文字数を最大18文字に制約
<!– 2 –>artist要素
メンバーは最大1人に制約(メンバーが2人以上はNG)
3つのファイルはすべて同じフォルダーに格納する
cd.xml・cd.xsd・artist.xsdはすべて同じフォルダーに格納してください。
先の章『「XMLスキーマファイル(*.xsd)」ではなく「XMLファイル(*.xml)」を選択』において、 スキーマファイル(*.xsd) にしなかった理由として、「複数のスキーマファイルがあるとどれを選択すれば良いか解らない」事を上げました。
cd.xsdとartist.xsdの構造が解っていれば、cd.xsdを選ぶのは難しくない認識です。
ところで対応付けに際して「XMLスキーマファイル(*.xsd)」を選んだ場合、「複数のグローバル宣言された要素が含まれているスキーマファイルではどのグローバル宣言された要素を選択すれば良いか解らない」事を上げましたが、実際に対応付けでcd.xsdファイルを選択してご説明いたします。
[1]「XMLの対応付け」で「追加」ボタンをクリックすると「XMLソースの選択」ウインドウが開きます。
ここでcd.xsdを選択し「開く(O)」ボタンをクリックします。
[2]すると「複数ルート」ウインドウが開きます。
※このウインドウに書かれた説明は少し解り難いのですが、「ルート ノード」とはグローバル宣言された要素や属性を意味しています。
サンプルファイルcd.xsdのルート要素は「discs」になるので、「discs」をクリックしてから「OK」ボタンを押します。
[3]「XMLの対応付け」に戻りますので「OK」ボタンをクリックします。
[4]「階層リスト」からXML要素を選び、ワークシートのセルに対応付けます。
XMLファイルがスキーマファイル(.xsd)を「参照している」場合とは異なり、対応付けしたセルにカーソルが当たった状態であっても、「開発」タブの「XML」グループにある「データの更新」は有効になりません。
「XMLスキーマファイル(*.xsd)」を選んだ場合は、「開発」タブの「XML」グループにある「インポート」を使用するのですが、今回は「インポート」は使わないので説明は省略致します。
※青字の箇所は先の章でご説明しています。(以下同様)
XMLファイルを読み込む際のXSDファイルの働きを検証する
大変長くなってしまいましたが、ようやく今回の本題に入ります。
①前章の「cd.xml・cd.xsd・artist.xsd」をコピペして3つのファイルを作り、すべて同じフォルダーに格納します。
②「XML ソース」作業ウィンドウの「XMLの対応付け…」で上記フォルダーを開き「cd.xml」を選択した後、「階層リスト」からXML要素を選び、ワークシートのセルに対応付けます。
XMLスキーマの検証機能を有効化する
③セルの対応付けが終わったら、対応付けしたセルにカーソルが当たった状態で、「開発」タブの「XML」グループにある「対応付けのプロパティ」をクリックします。
④ウインドウが開くので、「XMLスキーマの検証」の項目にある「インポートまたはエクスポート時にスキーマに対してデータを検証する」チェックボックスをONにします。
大変残念なことに、このチェックボックスはデフォルトでONにはなりません。
「OK」ボタンをクリックしてウインドウを閉じます。
XSDファイルに設定されている制約が反映されている事を確認する-その1
⑤「セルにXMLファイルのデータを読み込む」操作をすると「XMLインポートエラー」のウインドウが開くので、「詳細(D)…」ボタンをクリックします。
⑥「XMLエラー」のウインドウが開きますので、エラーメッセージの内容を確認します。
「原因」は解り難いのですが、どうやら「artist要素に予期していなかった事が起こった」と言っているようです。
実はartist.xsdでは「メンバーは最大1人に制約(メンバーが2人以上はNG)」に設定しているのに対して、cd.xmlでは2人データがあるartist要素が存在しています。
従いましてこのエラーは想定内です。
⑦「OK」ボタンを押してワークシートに戻ると、途中までセルにcd.xmlのデータが読み込まれている事が解ります。(artist要素考慮すると全部で3レコードあるはずですが、その内の2レコードが読み込まれています)
このような形で、XSDファイルに設定されている制約が機能する事が解りました。
「XMLインポートエラー」になった時の対応方法
基本的にエラーが起こった時は、XMLファイルのデータの誤りを直してから再度「セルにXMLファイルのデータを読み込む」操作をする事になるはずです。
ただし場合によってスキーマの制約を変更した方が手数が少なく対応できるケースもあります。
しかしスキーマファイル(*.xsd)を修正するとなると、実は「XML ソース」作業ウィンドウからの「XMLの対応付け…」ウインドウの中には「追加」と「削除」のボタンしかなく、つまり修正する場合は一度「削除」してから再度「追加」し直す必要があります。
「XMLの対応付け…」を削除する
⑧「このブック内のXMLの対応付け」から対象の名前を選択し、「削除」ボタンをクリックします。
⑨「このXMLの対応付けを削除すると、このXMLの対応付けを使用してXMLデータをインポートまたはエクスポートする事ができなくなります」という警告メッセージが表示されますので、「OK」ボタンをクリックします。
⑩ 「XMLの対応付け…」 ウインドウに戻ると選択した対応付けが消えていますので、先ほどご説明した①から④の作業を再度し直す事になります。
なお今回は確認のために「artist.xsd」のartist要素に「maxOccurs=”1″」を設定していますが、そもそもこの制約は盤実的ではないので、これを「maxOccurs=”unbounded”」に修正して再度対応付けをし直す事にします。
XSDファイルに設定されている制約が反映されている事を確認する-その2
artist.xsdのartist要素を「maxOccurs=”unbounded”」に修正したので、cd.xmlファイルはそのままで「セルにXMLファイルのデータを読み込む」操作をします。
⑪ところが、また「XMLインポートエラー」のウインドウが現れてしまいます。
実はartist.xsdには2ヶ所、非現実的な制約を設定してあります。
従いましてこのエラーも想定内になります。
⑫XMLエラーの原因には想定通りの『「Michael Edward Love」が18文字の制約に違反している』という事が書かれています。
⑬ただしワークシートに戻ると、制約の対象になったデータか「C4セル」にそのまま読み込まれている事が解ります。
読み込まれているのであれば、XMLファイルのデータを修正するまでも無いのですが、本来はエラーの原因を取り除いて置くのが良い認識です。
まとめ
「XSDファイルに設定されている制約が反映されている事を確認する」の「その1」・「その2」の結果から次の事が解ります。
- 「XMLスキーマの検証」で「XMLインポートエラー」に表示されるのは、恐らく先に見つかったエラーで、複数エラーがある場合でも1つづつしか表示されない。
- XSDファイルに設定されている制約の内容によって、セルにXMLファイルのデータが「読み込まれたり・読み込まれなかったり」する。
残念ながらExcelにおける「XMLスキーマの検証」の機能は有効ではあるものの、「エラーを潰す」という観点からは使い勝手が悪い印象です。
更に水を差す話なのですが実はExcelではサポートされないスキーマ構造が存在致します。
次回はサポートされない具体的な構造について動かせるサンプルファイルを使いながらご説明いたします。
以上最後までご一読いただき誠にありがとうございました。
