ExcelでXMLファイルを読み込む際のXSDファイルの働き－その1

「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マップ」と書かれている)ボタンを使用する事にします。

EXCEL の XML の概要

Excel で XML を使う方法と、それを使ってさまざまなデータセットを簡単に扱えるようにする方法を学習します。

support.microsoft.com

開発タブの表示方法につきましてはMicrosoft Officeサポートの下記のページをご参照ください。

[開発] タブを表示する

必要なときにリボンに タブを追加します。

support.microsoft.com

「XMLスキーマファイル(*.xsd)」ではなく「XMLファイル(*.xml)」を選択

先の章の「XMLの対応付け…」ボタンをクリックすると下左図のような「XMLの対応付け」ウインドウが開きます。

ここでカーソルが当たっている「追加」ボタンをクリックすると下右図のような「XMLソースの選択」ウインドウが開きます。

この時に指定できるファイルの拡張子は「XMLファイル(*.xml)」か「XMLスキーマファイル(*.xsd)」のいずれかになります。

前章でご紹介したMicrosoft Officeサポートの「EXCEL の XML の概要」の中の「Excel で XML データを使う基本的なプロセス」の章では、「XMLスキーマファイル(*.xsd)」を追加するところから始まっています。

ただし今回は「XMLファイル(*.xml)」を選択する事にしています。その理由は下記になります。

グローバル宣言とローカル宣言

詳細につきましては下記@ITの「最終回　XML Schema―型の再利用と名前空間」をご参照いただきたのですが、簡単に内容をまとめておきます。

＠IT：XMLマスターへの道 最終回　XML Schema―型の再利用と名前空間

atmarkit.itmedia.co.jp

• グローバル宣言
  • ルート要素直下の子要素として宣言されている「要素(=element」や「属性(=attribute)」や「型(=type)」の宣言
  • 読み込まれているスキーマファイルも含め、どこからでも参照可能
• ローカル宣言
  • ルート要素直下の子要素ではなく、ある要素の子要素として宣言されている「要素(=element」や「属性(=attribute)」や「型(=type)」の宣言
  • 記述されている子要素の括りの中でのみ参照可能

『スキーマファイル(.xsd)を「参照している」XMLファイル』を選択する場合

前章で「XMLファイル(*.xml)」を選んだ場合、XMLファイルがスキーマファイル(.xsd)を「参照しているか?」、「参照していないか?」によってその後の動き方が異なります。

「参照していない」場合につきましては今回のテーマから外れるのですが、操作方法についてまとめておきます。

XMLファイルがスキーマファイル(.xsd)を「参照していない」場合の操作方法

• 

• 

• 

1. 選んだXMLファイルがスキーマファイル(.xsd)を「参照していない」場合、左図①のメッセージが表示されるので、「OK」ボタンをクリックします。
2. 「XMLの対応付け」ウインドウに「推測されたスキーマ」が登録されます。
3. 「XMLソース」ウインドウには「XMLファイルで定義されているタグに基づいてExcelが推測したスキーマが「階層リストで表示」されます。

注意すべきポイント

• XMLファイルに複数の名前空間が定義されている場合は『「ns」+連番』の接頭辞が自動附番されます。
• 当然ではありますが、XMLファイルに定義されているタグが「スキーマで定義されている要素の一部しか含まれていない場合」は、「推測できるスキーマは一部分だけ」になります。
• スキーマで定義される型や属性も「データありき」のものなので制約にはなりません。

以上が「参照していない」場合の操作方法になります。

なお「XML名前空間」につきましては、下記@ITの「XML名前空間でタグを使い分ける」をご参照いただければ幸いです。ただし約20年前の2002年の記事であるためスキーマ言語は新しいXSDではなく古いDTDで表現されていますのでお含み置きください。

XML名前空間でタグを使い分ける

XML文書をさまざまなアプリケーションに処理させたいときなど、1つのXML文書に複数のボキャブラリを混在させたいことがある。そのためには、当初のXML文法に含まれていなかった「名前空間」という新しい考え方を導入する必要がある。本連載第6回は、XMLを飛躍的に使いやすくする「名前空間」の基本を解説する。

atmarkit.itmedia.co.jp

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 マップのセルに対応付ける

Excel ブックへの XML の対応付けを追加および削除する方法を説明します。

support.microsoft.com

XML要素が「繰り返す」場合と繰り返さない「単一」の場合で見出しの設定の仕方が異なるのですが、 今回は「繰り返す」場合で、「階層リスト」 から「① XML要素を選択 するケース」と「②すべての要素を選択するケース」の2つのパターンについて、キャプチャーを使ってポイントだけご説明いたします。

• 「階層リスト」 からXML要素を複数選ぶためには、要素を選択した後、つぎの要素を選択するためには「ctrlキー」を押しながら要素をクリックします。
  • 全要素を選択する場合はルート要素を選択します。
• すべての要素を選択し終えたら、XMLファイルからデータを貼り込む「先頭のセル」をワークシート上で選択します。
• その後「階層リスト」の選択した要素の上にカーソルを合わせ、その状態でマウスの右ボタンをクリックしてショートカットメニューを表示させて「要素の対応付け(M)…」をクリックします。

• 「XML要素の対応付け」ウインドウが開き、「XML要素を対応付け場所を指定してください」のテキストボックスにはデフォルトでセル範囲が入力されているので、それで良いかを確認して「OK」ボタンをクリックします。

• 「繰り返し要素」 の場合は、セル範囲にテーブルが設定されて最初の行には「階層リスト」に表示されている接頭辞付き要素名が列ヘッダーとしてセットされます。

これでXML要素をワークシートのセルに対応付けるための操作は完了です。

ワークシートのセルにXMLファイルのデータを読み込む

前章までで読み込むための準備がすべて終わったので、実際にXMLファイルのデータをワークシートに取り込むの操作に移ります。

今回の操作方法では「XMLの対応付け…」でXMLファイルを選択しているので、取り込むためのファイルは既に指定してある状態です。

そのために「開発」タブの「XML」グループにある「データの更新」が有効になっていますので、クリックするとデータがワークシートに反映されます。

長くなりましたが、以上でデータを読み込むまでの一通りの操作説明が終わりましたので、次の章から実際に動かせるサンプル使ってXSDの働きについて確認して行きます。

検証するためのサンプルファイル

複数のスキーマファイル(*.xsd)が設定されているサンプルをGoogleで検索したところ、下記のサイトにサンプルファイルが掲載されているのが見つかりました。

XMLにおける名前空間 - データ記述とXML

www.mlab.im.dendai.ac.jp

東京電機大学の学生向けに書かれた講義資料のようで、講義で使うために構造を簡略化しているものと推察いたします。

ただこのままでは今回の説明に必要な要素が少し足りていなかったので、構造を参考にさせていただきながら一部変更を致しました。

太字の要素はスキーマファイル(*.xsd)を分けています。

スキーマを分ける理由はいくつかある事と存じますが3つ程上げておきます。

• 要素名が重ならないようにする時
  • 別々のXMLLファイルを1つにまとめた時などに発生し易い。
• スキーマファイル(*.xsd)が複雑になり、行数が多くなった時
• 別なXMLファイルで、分割したスキーマをそのまま使える時

今回は、cd.xml・cd.xsd・artist.xsdの3つのファイルがそれそれ30行ぐらいになるように加工しています。

なおスキーマの要素、属性、データ型などの説明につきましては、2004年に書かれた記事ですが下記@ITの連載「SEのためのXML Schema入門」全6回をご参照いただければ幸いです。

SEのためのXML Schema入門

「SEのためのXML Schema入門」の連載記事一覧です。

atmarkit.itmedia.co.jp

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>

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>

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>

3つのファイルはすべて同じフォルダーに格納する

cd.xml・cd.xsd・artist.xsdはすべて同じフォルダーに格納してください。

先の章『「XMLスキーマファイル(*.xsd)」ではなく「XMLファイル(*.xml)」を選択』において、 スキーマファイル(*.xsd) にしなかった理由として、「複数のスキーマファイルがあるとどれを選択すれば良いか解らない」事を上げました。

cd.xsdとartist.xsdの構造が解っていれば、cd.xsdを選ぶのは難しくない認識です。

ところで対応付けに際して「XMLスキーマファイル(*.xsd)」を選んだ場合、「複数のグローバル宣言された要素が含まれているスキーマファイルではどのグローバル宣言された要素を選択すれば良いか解らない」事を上げましたが、実際に対応付けでcd.xsdファイルを選択してご説明いたします。

XMLファイルを読み込む際のXSDファイルの働きを検証する

大変長くなってしまいましたが、ようやく今回の本題に入ります。

XMLスキーマの検証機能を有効化する

XSDファイルに設定されている制約が反映されている事を確認する-その1

このような形で、XSDファイルに設定されている制約が機能する事が解りました。

「XMLインポートエラー」になった時の対応方法

基本的にエラーが起こった時は、XMLファイルのデータの誤りを直してから再度「セルにXMLファイルのデータを読み込む」操作をする事になるはずです。

ただし場合によってスキーマの制約を変更した方が手数が少なく対応できるケースもあります。

しかしスキーマファイル(*.xsd)を修正するとなると、実は「XML ソース」作業ウィンドウからの「XMLの対応付け…」ウインドウの中には「追加」と「削除」のボタンしかなく、つまり修正する場合は一度「削除」してから再度「追加」し直す必要があります。

「XMLの対応付け…」を削除する

なお今回は確認のために「artist.xsd」のartist要素に「maxOccurs=”1″」を設定していますが、そもそもこの制約は盤実的ではないので、これを「maxOccurs=”unbounded”」に修正して再度対応付けをし直す事にします。

XSDファイルに設定されている制約が反映されている事を確認する-その2

artist.xsdのartist要素を「maxOccurs=”unbounded”」に修正したので、cd.xmlファイルはそのままで「セルにXMLファイルのデータを読み込む」操作をします。

まとめ

「XSDファイルに設定されている制約が反映されている事を確認する」の「その1」・「その2」の結果から次の事が解ります。

残念ながらExcelにおける「XMLスキーマの検証」の機能は有効ではあるものの、「エラーを潰す」という観点からは使い勝手が悪い印象です。

更に水を差す話なのですが実はExcelではサポートされないスキーマ構造が存在致します。

次回はサポートされない具体的な構造について動かせるサンプルファイルを使いながらご説明いたします。

以上最後までご一読いただき誠にありがとうございました。