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

ExcelのTips
この記事は約29分で読めます。

「ExcelのTips」ではExcelを使用していた気付いたことを取り上げて行きます。

ExcelでXMLファイル読み込む(XMLデータのインポート)際のXMLスキーマファイル(.xsd)についての話題の第2回目になります。

第1回では複数のスキーマファイル(*.xsd)が設定されているXMLファイルでのXSDファイルの働きを確認しましたが、今回はExcelではサポートされないスキーマ構造についてサンプルファイを使いながらご説明いたします。

Excel 2016バージョン2107(ビルド 14228.20250)を使用しています。

スポンサーリンク

Excelではサポートされないスキーマ構造

出鼻をくじくような話題になりますが、Microsoft Officeサポートに下記のようなページがあります。

ただしスキーマ言語XSDの「要素」や「属性」までも日本語に訳されてしまっているので、かえって理解し難くなっている箇所があります。そこで英語でのページも併せてご紹介いたします。

これにより スキーマ言語 XSDの次の要素や属性は使用できない事が分かります。

  • <any>
  • <anyAttribute>
  • <substitutionGroup>

それ以外は下記の3項目になります。

  • 再帰構造—Recursive structures
  • 抽象要素—Abstract elements
  • 混在したコンテンツ—Mixed content

Excelのようなテーブル構造で「再帰構造」を対象にする事は難しい認識ですが、それ以外の5つの項目については「実際どうなるのか?」を1つづつ動かしながら確認して行きたいと思います。

<any>要素と<anyAttribute>要素

先ほどのMicrosoft officeサポートのページに書かれている行間を読むと、「これらをスキーマファイルで使うとスキーマファイルで宣言されていない要素・属性をXMLファイルのデータに含める事ができるため、セキュリティ的に良くないからサポートしない」となる認識です。

any要素が使われているスキーマファイル(*.xsd)のサンプルが無いか?Googleで検索したところ、 W3Schools(英語)のサイトに、any・anyAttribute・Mixed contentに関して一部に手を加えれば第1回でご説明した操作方法で動かせるサンプルが提供されている事が解りました。

なおW3Schoolsは「About Us」によると「1998年に創設されて、WWW(World Wode Web)から名前を引用しているものの、W3Cとは無関係」と書かれていますが、Web系で使用される数多くの言語のチュートリアルが公開されています。

英語での提供ですが、要素・属性の日本語化に注意すれば、最近のブラウザーの翻訳機能で日本語に変換すれば十分に理解できるのではないか?と思います。

any要素を使用しているサンプル

下記w3schoolsのページのサンプルを元にほんの一部修正をしています。

Myfamily.xmlの構造は下記のようになります。

ABCD
1persons
2person
3firstname
4lastname
5children
6childrenname
説明
←personの集合。ルート要素
←個人
←名前
←苗字
←子供の集合(無くても可)。any要素
←子供の名前

スキーマファイル(*.xsd) はfamily.xsd(細字)とchildre.xsd(太字)が使われています。

Myfamily.xml

<?xml version="1.0" encoding="utf-8"?>
<persons xmlns="family-ns"
 xmlns:chdn="children-ns"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="family-ns family.xsd
     children-ns children.xsd"><!-- 1 -->
  <person>
    <firstname>Hege</firstname>
    <lastname>Refsnes</lastname>
    <chdn:children><!-- 2 -->
      <chdn:childname>Cecilie</chdn:childname>
      <chdn:childname>Wesley</chdn:childname>
      <chdn:childname>Austin</chdn:childname>
    </chdn:children>
  </person>
  <person>
    <firstname>Stale</firstname>
    <lastname>Refsnes</lastname>
  </person>
</persons>

<!– 1 –>
family.xsdとchildren.xsdのschemaLocationの設定です。

青字の箇所は第1回でご説明しています。(以下同様)

<!– 2 –>
次にご説明するfamily.xsdではchildren.xsdに関する情報は何も設定していないので、XMLファイルのルート要素で接頭辞を使って名前空間の指定をしています。

なおchildren.xsdスキーマファイルでは「childnameは2つまで」という制約を設定しているので「Austin」は制約に引っかかるようにわざと設定しています。

family.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
 targetNamespace="family-ns"
  xmlns="family-ns"
  elementFormDefault="qualified"><!-- 1 -->
<!-- persons -->
  <xs:element name="persons" type="personsType"/>
  <xs:complexType name="personsType">
    <xs:sequence>
      <xs:element maxOccurs="unbounded" ref="person"/>
    </xs:sequence>
  </xs:complexType>
<!-- person -->
  <xs:element name="person">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="firstname" type="xs:string"/>
        <xs:element name="lastname" type="xs:string"/>
        <xs:any minOccurs="0"/><!-- 2 -->
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

<!– 1 –>
family.xsdの中には自分以外の名前空間は定義されていないのですが、elementFormDefaultがqualifiedになっていないと、Excel以外のXMLツール(例えば「XML Notepad」)ではエラーが返ります。

恐らくXMLファイルの構造から判断されるものと思われます。

<!– 2 –>
any要素がここで使われていますが、名前空間の指定などは無く、XMLファイルのデータの内容から要素が定まる認識です。

「XML Notepad」について

「XML Notepad」については本記事ではご説明を省きますが、下記サイトからインスタンスする事ができます。

なお「XML Notepad」はMicrosoftのOpenCodeになります。

children.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  targetNamespace="children-ns"
  xmlns="children-ns"
  elementFormDefault="qualified"><!-- 1 -->
<!-- children -->
  <xs:element name="children">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="childname" type="xs:string"
         maxOccurs="2"/><!-- 2 -->
<!-- maxOccurs="unbounded"/> -->
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

<!– 1 –>
elementFormDefaultがqualifiedになっていないと、「XML Notepad」ではエラーが返ります。

<!– 2 –>
本来はmaxOccurs=”2″ではなく、次のコメント行のmaxOccurs=”unbounded”にすべきですが、children.xsdスキーマファイルの動作を確認するためにわざと設定してあります。

Excel「XML ソース」作業ウィンドウの「XMLの対応付け…」で開く

①Myfamily.xmlを「XML ソース」作業ウィンドウの「XMLの対応付け…」から「追加」設定をしようとすると、「以下のスキーマ要素と構造はワークシートに対応付ける事ができないため、[XMLソース]作業ウインドウには表示されません」のメッセージが表示されます。

「ブックへのスキーマの追加を続行しますか?」という内容なので「はい(Y)」ボタンをクリックします。

②「XMLの対応付け」には登録内容が反映されますので、「OK」ボタンをクリックします。

③XMLソースの「階層リスト」にはchildrenの要素は含まれていません。

④念のためすべてのXML要素をワークシートのセルに対応付けて、「開発」タブの「XML」グループにある「データの更新」によりXMLファイルのデータをワークシートに取り込んでみると、やはりchildren要素のデータは反映されません。

children.xsdの働きを「XML Notepad」で確認する

「XML Notepad」でMyfamily.xmlを開くと、解り難い表現ではありますが、「’children’に無効な…’childname’があります」というエラーが表示されます。

これはXMLファイルで制約違反になるようにわざと設定した「Austin」を指しています。

エラーは「左図最下部の青い背景のライン」に表示されています。

このことからchildren.xsdスキーマファイルは正しく機能している事が解りますが、Excelにはany要素で設定された箇所のXMLファイルのデータは反映されないという結果になります。

【番外】Excelの「推測されたスキーマ」を使った場合

第1回で「XMLファイルがスキーマファイル(.xsd)を「参照していない」場合の操作方法」をご説明しましたが、この場合にExcelは「推測されたスキーマ」を作り出します。

<persons xmlns="family-ns"
 xmlns:chdn="children-ns"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- xsi:schemaLocation="family-ns family.xsd
     children-ns children.xsd">--><!-- 1 -->

今回のサンプルのXMLファイルでschemaLocation設定を左図のように注釈にすると「XMLソース」ウインドウには「XMLファイルで定義されているタグに基づいてExcelが推測したスキーマが「階層リストで表示」されます。

この推測されたスキーマを使ってワークシートのセルにXMLファイルのデータを読み込むと左図①のように取敢えずデータを取り込む事はできますが、

XMLファイルのデータ構造がスキーマファイル(*.xsd)の構造と差が生じないような結果になる時は、推測されたスキーマを使ってデータを取敢えず取り込んでみる事で、Excelでサポートされないスキーマ構造に対処するための一案になるかもしれません。

ただしスキーマファイルに設定している制約に関しては人が判断する必要があります。左図4行目の「Austin」は制約違反です。

anyAttribute属性を使用しているサンプル

下記w3schoolsのページのサンプルを元にほんの一部修正をしています。

前章と同じ名前のMyfamily.xmlの構造は下記のようになります。ただし前章とは一部違いがあります。

ABCD
1persons
2person
3eyecolor
4firstname
5lastname
説明
←personの集合。ルート要素
←個人
←アイカラー。属性
←名前
←苗字

スキーマファイル(*.xsd) はfamily.xsd(細字)とperson要素の属性を定義しているattribute.xsd(太字)が使われています。

Myfamily.xml

<?xml version="1.0" encoding="utf-8"?>
<persons xmlns="family-ns"
 xmlns:atrb="attribute-ns"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="family-ns family.xsd
     attribute-ns attribute.xsd"><!-- 1 -->
  <person atrb:eyecolor="green">
    <firstname>Hege</firstname>
    <lastname>Refsnes</lastname>
  </person>
  <person atrb:eyecolor="purple"><!-- 2 -->
    <firstname>Stale</firstname>
    <lastname>Refsnes</lastname>
  </person>
</persons>

<!– 1 –>
family.xsdとattribute.xsdのschemaLocationの設定です。

<!– 2 –>
次にご説明するfamily.xsdではattribute.xsdに関する属性情報は何も設定していないので、XMLファイルのルート要素で接頭辞を使って名前空間の指定をしています。

なおeyecolor属性に「purple」は定義されていません。
エラー検証のためにわざと設定してあります。

family.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
 targetNamespace="family-ns"
  xmlns="family-ns"
  elementFormDefault="qualified"><!-- 1 -->
<!-- persons -->
  <xs:element name="persons" type="personsType"/>
  <xs:complexType name="personsType">
    <xs:sequence>
      <xs:element maxOccurs="unbounded" ref="person"/>
    </xs:sequence>
  </xs:complexType>
<!-- person -->
  <xs:element name="person">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="firstname" type="xs:string"/>
        <xs:element name="lastname" type="xs:string"/>
      </xs:sequence>
      <xs:anyAttribute/><!-- 2 -->
    </xs:complexType>
  </xs:element>
</xs:schema>

<!– 1 –>
elementFormDefaultがqualifiedになっていないと、「 XML Notepad 」ではエラーが返ります。

<!– 2 –>
anyAttribute属性がここで使われていますが、名前空間の指定などは無く、XMLファイルのデータの内容から属性が定まる認識です。

attribute.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  targetNamespace="attribute-ns"
  xmlns="attribute-ns"
  elementFormDefault="qualified"><!-- 1 -->
<!-- eyecolor -->
  <xs:attribute name="eyecolor">
    <xs:simpleType>
        <xs:restriction base="xs:string"><!-- 2 -->
          <xs:pattern value="blue|brown|green|grey"/>
        </xs:restriction>
    </xs:simpleType>
  </xs:attribute>
</xs:schema>

<!– 1 –>
elementFormDefaultがqualifiedになっていないと、「XML Notepad 」ではエラーが返ります。

<!– 2 –>
eyecolor属性の値として設定しているのは、「blue|brown|green|grey」の4色です。

Excel「XML ソース」作業ウィンドウの「XMLの対応付け…」で開く

①Myfamily.xmlを「XML ソース」作業ウィンドウの「XMLの対応付け…」から「追加」設定をしようとすると、「以下のスキーマ要素と構造はワークシートに対応付ける事ができないため、[XMLソース]作業ウインドウには表示されません」のメッセージが表示されます。

②XMLソースの「階層リスト」にはeyecolorの属性は含まれていません。

attribute.xsdの働きを「XML Notepad」で確認する

「XML Notepad」でMyfamily.xmlを開くと、「’attribute-ns:eyecolor’属性は無効です。値’purple’はテータ型’String’に対して無効です。Pattern制約が失敗しました」「Pattern制約が失敗しました」という2つのエラーが表示されますが、どちらも同じLine11行目になっているので、同じエラーになります。

このことからatribute.xsdスキーマファイルは正しく機能している事が解りますが、ExcelにはanyAttribute要素で設定された箇所のXMLファイルのデータは反映されないという結果になります。

なお「XML Notepad」でも制約に違反したデータは読み込まれて表示されるようです。

【番外】Excelの「推測されたスキーマ」を使った場合

今回のサンプルのXMLファイルでschemaLocation設定を注釈にすると「XMLソース」ウインドウには「XMLファイルで定義されているタグに基づいてExcelが推測したスキーマが「階層リストで表示」されます。

この推測されたスキーマを使ってワークシートのセルにXMLファイルのデータを読み込むと左図①のように取敢えずデータを取り込む事はできます。

XMLファイルのデータ構造がスキーマファイル(*.xsd)の構造と差が生じないような結果になる時は、推測されたスキーマを使ってデータを取敢えず取り込んでみる事で、Excelでサポートされないスキーマ構造に対処するための一案になるかもしれません。

なお制約違反の「purple」が取り込まれていますので人が判断する必要があります。

Mixed content—混在したコンテンツ

下記w3schoolsのページのサンプルを元にほんの一部修正をしています。

letter.xmlの構造は下記のようになります。

ABC
1letters
2letter
3name
4orderid
5shipdate
説明
←letterの集合。ルート要素
←レター
←名前
←注文番号
←出荷日

スキーマファイル(*.xsd) はletter.xsdが使われます。

letter.xml

<?xml version="1.0" encoding="utf-8"?>
<letters xmlns="letter-ns"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="letter-ns letter.xsd"><!-- 1 -->
  <letter><!-- 2 -->
    Dear Mr.<name>John Smith</name>.
    Your order <orderid>1032</orderid>
    will be shipped on <shipdate>2001-07-13</shipdate>.
  </letter>
  <letter>
    Dear Mr.<name>Andrew Jackson</name>.
    Your order <orderid>101233</orderid>
    will be shipped on <shipdate>2021-07-31</shipdate>.
  </letter>
</letters>

<!– 1 –>
letter.xsdのschemaLocationの設定です。

<!– 2 –>
letter要素には要素タグの括り以外の子要素の間に固定のメッセージがセットされている、混合コンテンツになります。

letter.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  targetNamespace="letter-ns"
  xmlns="letter-ns"
  elementFormDefault="qualified"><!-- 1 -->
<!--letters-->
  <xs:element name="letters" type="lettersType"/>
  <xs:complexType name="lettersType">
    <xs:sequence>
      <xs:element maxOccurs="unbounded" ref="letter"/>
    </xs:sequence>
  </xs:complexType>
<!--letter-->
  <xs:element name="letter">
    <xs:complexType mixed="true"><!-- 2 -->
      <xs:sequence>
        <xs:element name="name" type="xs:string"/>
        <xs:element name="orderid" type="xs:positiveInteger"/>
        <xs:element name="shipdate" type="xs:date"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

<!– 1 –>
elementFormDefaultがqualifiedになっていないと、「 XML Notepad 」ではエラーが返ります。

<!– 2 –>
w3schoolsのページで『「letter」の子要素の間に文字データを表示できるようにするには、mixed属性を「true」に設定する必要があります。』と書かれています。

Excel「XML ソース」作業ウィンドウの「XMLの対応付け…」で開く

①letter.xmlを「XML ソース」作業ウィンドウの「XMLの対応付け…」から「追加」設定をしようとすると、「以下のスキーマ要素と構造はワークシートに対応付ける事ができないため、[XMLソース]作業ウインドウには表示されません」のメッセージが表示されます。

②XMLソースの「階層リスト」にはletter要素のすべての子要素が含まれています。

③そこですべてのXML要素をワークシートのセルに対応付けて、「開発」タブの「XML」グループにある「データの更新」によりXMLファイルのデータをワークシートに取り込んでみると、残念ながら「letter」の子要素の間に文字データは反映されません。

letter.xsdの働きを「XML Notepad」で確認する

「XML Notepad」でletter.xmlを開くと、左図のように要素の間に文字データも取り込まれて表示されます。

このことからletter.xsdスキーマファイルは正しく機能している事が解りますが、Excelにはmixed属性が「true」に設定された要素の子要素の間の文字データは反映されないという結果になります。

【番外】Excelの「推測されたスキーマ」を使った場合

XMLファイルに定義されているタグに基づいてExcelが推測するものなので、タグの括りの外にある子要素の間の固定のメッセージを拾う事はできません。

Abstract elements—抽象要素とsubstitutionGroup

substitutionGroupのサンプルはw3schoolsにもあるのですが、例題の内容がいまいち把握し難いところがあったので、Googleで検索したところStack Exchange社が運営するstackoverflowという質問サイトに掲載されていた下記のサンプルがありました。これを元にほんの一部修正を加えて使用しています。

sea.xmlの構造は下記のようになります。

ABC
1Seas
2Sea
3Tuna
4Squid
5Salmon
説明
←Seaの集合。ルート要素
←海洋
←マグロの種類(順不同)
←イカの種類(順不同)
←サーモンの種類(順不同)

スキーマファイル(*.xsd) はsea.xsdが使われます。

sea.xml

<?xml version="1.0" encoding="utf-8"?>
<Seas xmlns="sea-ns"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="sea-ns sea.xsd"><!-- 1 -->
  <Sea>
    <name>Atlantic Ocean</name>
    <Tuna>Bluefin tuna</Tuna>
    <Squid>Spear squid</Squid>
    <Squid>Aori squid</Squid>
    <Tuna>Yellowfin tuna</Tuna>
    <Salmon>King salmon</Salmon>
  </Sea>
  <Sea>
    <name>Pacific Ocean</name>
    <Tuna>Southern bluefin tuna</Tuna>
    <Sardine>Etrumeus sadina</Sardine><!-- 2 -->
    <Salmon>Pink salmon</Salmon>
  </Sea>
</Seas>

<!– 1 –>
sea.xsdのschemaLocationの設定です。

<!– 2 –>
要素「Sardine」はスキーマで定義されていません。
エラー検証のためにわざと設定してあります。

sea.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns="sea-ns" targetNamespace="sea-ns"
 xmlns:xs="http://www.w3.org/2001/XMLSchema">
<!--Seas-->
  <xs:element name="Seas" type="seasType"/>
  <xs:complexType name="seasType">
    <xs:sequence>
      <xs:element maxOccurs="unbounded" ref="Sea"/>
    </xs:sequence>
  </xs:complexType>
<!--Sea-->
  <xs:element name="name" type="xs:string"/>
  <xs:element name="Sea">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="name" minOccurs="0" maxOccurs="1"/>
        <xs:element ref="FishSubGroup" minOccurs="0"
         maxOccurs="unbounded"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
<!--FishSubGroup-->
  <xs:element name="FishSubGroup" type="xs:string"
   abstract="true"/><!-- 1 -->
  <xs:element name="Tuna" substitutionGroup="FishSubGroup"/>
  <xs:element name="Squid" substitutionGroup="FishSubGroup"/>
  <xs:element name="Salmon" substitutionGroup="FishSubGroup"/>
</xs:schema>

<!– 1 –>
実はこのabstract=”true”は無くても「XML Notepad」でエラーにはなりません。

ただしXMLファイルには現れない要素になるので、 abstract=”true” があれば可読性が上がる認識ですが、無くても成立するとなると、これをAbstract elementsの実装例として良いか?は悩ましいところです。

Excel「XML ソース」作業ウィンドウの「XMLの対応付け…」で開く

①letter.xmlを「XML ソース」作業ウィンドウの「XMLの対応付け…」から「追加」設定をしようとすると、「以下のスキーマ要素と構造はワークシートに対応付ける事ができないため、[XMLソース]作業ウインドウには表示されません」のメッセージが表示されます。

②XMLソースの「階層リスト」にはsubstitutionGroupに設定した要素は表示されません。

sea.xsdの働きを「XML Notepad」で確認する

「XML Notepad」でsea.xmlを開くと、左図のようにsubstitutionGroupに設定した要素も取り込まれて表示されます。

なお「要素名前空間’sea-ns’の’Sea’には無効な子要素名前空間’sea-ns’の’Sardine’が含まれています。…」というエラーが表示されますが、これはsea.xmlであらかじめセットしたエラーになりますので問題ありません。

このことからseaxsdスキーマファイルは正しく機能している事が解りますが、ExceではsubstitutionGroupに設定された要素は反映されないという結果になります。

【番外】Excelの「推測されたスキーマ」を使った場合

今回のサンプルのXMLファイルでschemaLocation設定を注釈にすると「XMLソース」ウインドウには「XMLファイルで定義されているタグに基づいてExcelが推測したスキーマが「階層リストで表示」されます。

この推測されたスキーマを使ってワークシートのセルにXMLファイルのデータを読み込むと左図①のように取敢えずデータを取り込む事はできます。

ただし本来取り込んではいけない要素「Sardine」までもシートに反映されていますので注意が必要です。

まとめ

Excelではサポートされない5つのスキーマ構造について、実際に動かせるサンプルファイルを使いながらご説明して参りました。

Excel以外のXMLデータを扱うアプリケーションでも「サポートされないスキーマ」は少なからず存在している認識です。

ただその解決策の一案にExcelの「推測されたスキーマ」が使えるか?は注意して判断する必要があります。

これまで第1回、2回でサンプルとしてのXMLファイルをご説明して参りましたが、次回第3回は最終回となりますが、実践として、国税庁の報告事項で使用されているXMLファイルをExcelで使用する方法を中心にご説明いたします。

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

ExcelのTips
スポンサーリンク
スポンサーリンク
シェアする
∞ワークスKenをフォローする
∞ワークス