XSDがどのような構造であればExcelでエクスポートできるのか?－その2 XMLの話題 Twitter 2021.11.17 2021.10.07 この記事は約38分で読めます。 「ExcelのTips」ではExcelを使用していて気付いたことを取り上げて行きます。 Excelでは「複雑なXMLファイルはエクスポートできない」ように言われていますが「実際はどうなのか?」について動かせるサンプルを使いながら「できる・できない」の線引きを探ります。 前回の第1回ではMicrosoft Docsに掲載されている「シンプルなスキーマ」を使ってmaxOcursが2の場合をご説明しましたが、今回はもう少し複雑な「購買発注書のスキーマ」を使ってmaxOccursがunboundedの場合について検討して見たいと思います。 ただしExcelに備わる機能だけで解決することはできません。何かしらのコーディングを使用する案になりますのであらかじめお含み置きいただければ幸いです。 Excel 2016バージョン2108(ビルド 14326.20348)を使用しています。 スポンサーリンク 目次 今回使用するサンプルデータ「XMLの対応付け…」で2つのセル範囲に分けて項目を対応付ける「購買発注書のスキーマ」を複数レコードに変更して確認するunboundedの「リストのリスト」を解消する方法はあるのか?物理的にスキーマファイルを2つに分割する分割したスキーマファイルでXMLファイルを読み込んで見る「関係性を示すキーとなる要素」をそれぞれのスキーマファイルに追加するプログラムを作ってXMLファイルにキー項目「orderNum」を追加するキー項目を付けたXMLファイルをそれぞれエクスポートするキー項目を使って2つのXMLファイルを1つにまとめるプログラムを作る合体されたXMLファイルで名前空間の接頭辞を書き換えるにはまとめ 今回使用するサンプルデータ 前回に引き続きMicrosoft Docsの「Visual StudioのXMLツール」に掲載されている「購買発注書のスキーマ」を使用します。 購買発注書のスキーマリンク先：https://docs.microsoft.com/ja-jp/visualstudio/xml-tools/sample-xsd-file-purchase-order-schema?view=vs-2019 上記サイト上でサンプルをコピーしてローカル環境にファイルを保存して使用します。ファイル名は「sample-02.xsd」としています。 このスキーマファイルの構造は下記のようになります。 ※緑色の文字は属性を表しています。 ◢ABCD1PurchaseOrder2OrderDate3confirmDate4ShipTo5country6name7street8city9state10zip11BillTo……18comment19items20item21partNum22productName23quantity24USPrice25comment26shipDate 説明←購買注文。ルート要素←注文日←確定日。必須←送り先←国←送り先名←番地←都市←州←郵便番号←請求先…子要素はShipToと同じため省略←コメント←品目群←品目。maxOccurs=”unbounded”←部品番号←商品名←数量←ドル価格←コメント←出荷日 「シンプルなスキーマ」と比べると構造が複雑になって、更に今回はitem要素のmaxOccurs属性がunboundedに設定されています。 「XMLの対応付け…」で2つのセル範囲に分けて項目を対応付ける 書き出し(エクスポート)の第1回で見たように、maxOccursが含まれる項目を全てまとめてセルに対応付けしても「例外的なデータ」になりエクスポートできません。 そこでmaxOccursが含まれるitems要素とそれ以外に分けてセルに対応付けします。 構造は複雑になっていますが、左図のようにmaxOccurs属性が設定されているitems要素を分ける事でエクスポートする事はできます。 「購買発注書のスキーマ」を複数レコードに変更して確認する 1レコードであれば、対応付けを2つに分けることでエクスポートできる事は解りましたが、これが複数レコードになった場合にはどうなるのでしょうか? 実際にスキーマファイルを複数レコードが扱えるように変更して確認します。なおスキーマファイル名はsample-02x.xsdにしています。 書き出し(エクスポート)の第1回で具体的なスキーマファイルの修正方法をご説明しましたが、今回もまったく同様にルート要素である「PurchaseOrder」に対して、maxOccursをunboundedに設定した新たにルート要素「PurchaseOrders」を追加します。 なお具体的な修正内容につきましてはお手数ですが第1回をご参照いただければ幸いです。 前章と同様にmaxOccursが含まれるitems要素とそれ以外に分けてセルに対応付けしています。 ただし「エクスボートする対応付けの確認」をすると左図のように「複数リストのリスト」というエラーメッセージが表示されてエクスポートする事はできませんでした。 前回の「シンプルなスキーマ」と今回の「購買発注書のスキーマ」は構造の複雑さは異なりますが、エクスポートしようとした時の結果は同じになります。 unboundedの「リストのリスト」を解消する方法はあるのか? 前回はmaxOccurs分の要素を名前付けして分ける事で対応しましたが、今回はunboundedになっているので同じような対応はできません。 考えられる解消方法のひとつとして「Excelのエクスポート機能を使わないでコーディングする」という選択肢もありますが、使いにくい点はあるものの「エクスポート時にスキーマに対してデータを検証する」事ができますのでエクスポート機能を使う前提で考える事に致します。 今回の「リストのリスト」になる要因はmaxOccursがunboundedの要素の子要素にmaxOccursがunboundedの要素が含まれている事です。 そこで「リストのリスト」の構造を「リスト」と「リスト」の2つ構造に分けてみる事にします。 物理的にスキーマファイルを2つに分割する このunboundedの「リストのリスト」状態を解消するために、それぞれのmaxOccurs属性を持つ要素を分けて別々のスキーマファイルにするとどのような動きになるか?を検証して見ます。 なお元々の「購買発注書のスキーマ」にはannotaion要素が設定されていますが、下記でご紹介するスキーマファイルでは割愛していますので、お含み置きください。 items要素以外のスキーマファイル ファイル名：sample02x-1.xsd PurchaseOrderTypeに定義されているpurchaseOrderの子要素のなかから、items要素を取り除きます。 なおcomment要素は2ヶ所で参照されているためにグローバル宣言されていますが、2つに分割するとそれぞれのスキーマファイルでは使用箇所は1ヶ所になるのでローカル宣言に変更します。 合わせてitems要素の子要素でのみ使われているSKU型も削除しています。 sample02x-1.xsd <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://tempuri.org/PurchaseOrderSchema.xsd" targetNamespace="http://tempuri.org/PurchaseOrderSchema.xsd" elementFormDefault="qualified"> <xsd:element name="purchaseOrders" type="tns:purchaseOrdersType"/> <xsd:complexType name="purchaseOrdersType"> <xsd:sequence> <xsd:element maxOccurs="unbounded" ref="tns:purchaseOrder"/> </xsd:sequence> </xsd:complexType> <xsd:element name='purchaseOrder' type='tns:PurchaseOrderType'/> <xsd:complexType name='USAddress'> <xsd:sequence> <xsd:element name='name' type='xsd:string'/> <xsd:element name='street' type='xsd:string'/> <xsd:element name='city' type='xsd:string'/> <xsd:element name='state' type='xsd:string'/> <xsd:element name='zip' type='xsd:decimal'/> </xsd:sequence> <xsd:attribute name='country' type='xsd:NMTOKEN' fixed='US'/> </xsd:complexType> <xsd:complexType name='PurchaseOrderType'> <xsd:sequence> <xsd:element name='shipTo' type='tns:USAddress'/> <xsd:element name='billTo' type='tns:USAddress'/> <xsd:element name='comment' type='xsd:string' minOccurs='0'/> </xsd:sequence> <xsd:attribute name='orderDate' type='xsd:date'/> <xsd:attribute name='confirmDate' type='xsd:date' use='required'/> </xsd:complexType> </xsd:schema> items要素だけ残したスキーマファイル ファイル名：sample-02x-2.xsd items要素だけを残すのですが、ルート要素からの道筋が閉ざされてしまうと、同じXMLファイルからの読み込み(インポート)ができなくなってしまいます。 そのためpurchaseOrders要素とpurchaseOrder要素は残しておく必要があります。 ただし前回の書き出し(エクスポート)の第1回目の『対応付けられた要素とその他の要素との関係を維持できない場合にはエクスポートできません』でご説明したように「シーケンスに直接の子要素が2つ以上定義されている」事は許されません。これは属性が定義されていても同様にNGです。 またpurchaseOrdersType型の中で参照しているpurchaseOrder要素にmaxOccurs属性がunboundedで定義されていますが、これをこのまま残していると「リストのリスト」のエラーを取り除く事はできません。 なおunboundedを取り除いた場合「XMLファイルでitems要素がどのように出力されるか?」につきましては後述いたします。 sample02x-2.xsd <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://tempuri.org/PurchaseOrderSchema.xsd" targetNamespace="http://tempuri.org/PurchaseOrderSchema.xsd" elementFormDefault="qualified"> <xsd:element name="purchaseOrders" type="tns:purchaseOrdersType"/> <xsd:complexType name="purchaseOrdersType"> <xsd:sequence> <xsd:element ref="tns:purchaseOrder"/> </xsd:sequence> </xsd:complexType> <xsd:element name='purchaseOrder' type='tns:PurchaseOrderType'/> <xsd:simpleType name='SKU'> <xsd:restriction base='xsd:string'> <xsd:pattern value='\d{3}\w{3}'/> </xsd:restriction> </xsd:simpleType> <xsd:complexType name='Items'> <xsd:sequence> <xsd:element name='item' minOccurs='0' maxOccurs='unbounded'> <xsd:complexType> <xsd:sequence> <xsd:element name='productName' type='xsd:string'/> <xsd:element name='quantity'> <xsd:simpleType> <xsd:restriction base='xsd:positiveInteger'> <xsd:minInclusive value='1'/> <xsd:maxExclusive value='100'/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name='USPrice' type='xsd:decimal'/> <xsd:element name='comment' type='xsd:string'/> <xsd:element name='shipDate' type='xsd:date' minOccurs='0'/> </xsd:sequence> <xsd:attribute name='partNum' type='tns:SKU'/> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> <xsd:complexType name='PurchaseOrderType'> <xsd:sequence> <xsd:element name='items' type='tns:Items'/> </xsd:sequence> </xsd:complexType> </xsd:schema> それぞれのスキーマファイルを「XMLの対応付け」に追加しセルに対応付ける 今回はXMLソースの「XMLの対応付け」に2つのスキーマファイルを追加します。 ①②③ ①「XMLの対応付け」に登録した名前は変更する事ができます。 デフォルトは「ルート要素の要素名」に「_対応付け」が付与される形ですが、ここではその最後に番号を付けています。 という事で「purchaseOrders_対応付け1」が最初のsample02x-1.xsd、「purchaseOrders_対応付け2」がsample02x-2.xsdになります。 ②「XMLソース」のプルダウンメニューから対応付けするスキーマファイルを選択します。 ③要素の対応付けをします。 なお追加した「XMLの対応付け」をセルに対応付けする場所は、同じワークシートの重ならない場所でも良いですし、別々のワークシートに対応付けしても良いのですが、ここでは別々のワークシートに対応付けしています。 という事でsheet1に「purchaseOrders_対応付け1」、sheet2に「purchaseOrders_対応付け2」を対応付けします。 これでそれぞれのスキーマファイルのセルへの対応付けが完了したので、次にエクスポートできるか?を確認します。 「XMLソース」のプルダウンメニューから確認する対応付けを選択してから「エクスポートする対応付けの確認…」のリンクをクリックします。 ①② ①まず「purchaseOrders_対応付け1」を選んで「エクスポートする対応付けの確認…」すると、エクスポートできる事が解ります。 ②次に「purchaseOrders_対応付け2」を選んで同様に確認すると、これもエクスポートできる事が解ります。 以上の結果から「リストのリスト」のエラーになった場合でもスキーマファイルを分割する事で、分割したスキーマファイルはともにエクスポートできる事が解りました。 ただし分割したからには、XMLファイルをエクスポート後に両者を合体させる必要がありますが、その考え方につきましては後述いたします。 分割したスキーマファイルでXMLファイルを読み込んで見る 前章の分割したスキーマファイルを使ってXMLファイルを読み込んだらどのようにセルにデータが反映されるかを確認して見ます。 スキーマファイルを2つに分割していますが、読み込むXMLファイルは1つで大丈夫な形にしています。 少し長くなりますがスキーマファイルsampe-02x.xsdを元に、内容としては架空ですが、Micorosoftの米国のセールスオフィスの住所情報をもとにsample-02x.xmlという名前のサンプルデータを作成しています。 サンプルデータの構成を下記の表にまとめました。 内容1レコード目2レコード目3レコード目items要素以外purchaseOrders_対応付け1shipTo…billTo…commentshipTo…billTo…shipTo…billTo…commentitems要素purchaseOrders_対応付け2item…item…item…なし item … 全体で3レコード1レコード目はcommentはありitemは3つ2レコード目はcommentはなくitemはなし3レコード目はcommentはありitemは1つ sample02x.xml <?xml version="1.0" encoding="UTF-8"?> <purchaseOrders xmlns="http://tempuri.org/PurchaseOrderSchema.xsd"> <purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <shipTo country="US"> <name>Corporate Sales Office: Boise</name> <street>401 W. Front Street, Suite 600</street> <city>Boise</city> <state>ID</state> <zip>83702</zip> </shipTo> <billTo country="US"> <name>Corporate Sales Office: Sacramento</name> <street>1415 L Street, Suite 200</street> <city>Sacramento</city> <state>CA</state> <zip>95814</zip> </billTo> <comment>Handle with care</comment> <items> <item partNum="001abc"> <productName>keyboard</productName> <quantity>3</quantity> <USPrice>11.10</USPrice> <comment>Buletooth</comment> <shipDate>2021-09-23</shipDate> </item> <item partNum="101acf"> <productName>mouse</productName> <quantity>5</quantity> <USPrice>10.90</USPrice> <comment>Wireless</comment> <shipDate>2021-09-23</shipDate> </item> <item partNum="201ffs"> <productName>pen</productName> <quantity>1</quantity> <USPrice>20.10</USPrice> <comment/> <shipDate>2021-09-23</shipDate> </item> </items> </purchaseOrder> <purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <shipTo country="US"> <name>Corporate Sales Office: Austin</name> <street>10900 Stonelake Boulevard, Suite 225</street> <city>Austin</city> <state>TX</state> <zip>78759</zip> </shipTo> <billTo country="US"> <name>Corporate Sales Office: Tulsa</name> <street>7633 E. 63rd Place, Suite 300</street> <city>Tulsa</city> <state>OK</state> <zip>74133</zip> </billTo> <items/> </purchaseOrder> <purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <shipTo country="US"> <name>Corporate Sales Office: Austin</name> <street>10900 Stonelake Boulevard, Suite 225</street> <city>Austin</city> <state>TX</state> <zip>78759</zip> </shipTo> <billTo country="US"> <name>Corporate Sales Office: Tulsa</name> <street>7633 E. 63rd Place, Suite 300</street> <city>Tulsa</city> <state>OK</state> <zip>74133</zip> </billTo> <comment>Handle with care</comment> <items> <item partNum="001abd"> <productName>keyboard1</productName> <quantity>1</quantity> <USPrice>11.11</USPrice> <comment>Buletooth</comment> <shipDate>2021-09-23</shipDate> </item> </items> </purchaseOrder> </purchaseOrders> ①②② ①「purchaseOrders_対応付け1」をセルに対応付けたsheet1ワークシートを選んでリボンメニューからインポートします。 ②この時に対応付けたセル範囲にフォーカスが当たってないと「対応付けを選択する画面」が表示されます。 対象の対応付けを選んでOKボタンをクリックすると、3つのレコードが読み込まれます。 ③同様に「purchaseOrders_対応付け2」をsheet2ワークシートを選んでからインポートします。 sheet2には4つのレコードが読み込まれます。 これにより、purchaseOrder要素からmaxOccurs属性を取り除いても、すべてのpurchaseOrder要素に属する子要素が読み込まれる事が解ります。 ただし、sheet1の3つのレコードとsheet2の4つのレコードを紐づけるための情報がありません。sheet1ワークシートは上から順に、1レコード目・2レコード目・3レコード目だとしても、sheet2の4つのレコードとの関係性が解りません。 従いまして、この状態で両者をエクスポートしたとしても、2つのXMLファイルを1つにまとめるのは「人が内容を把握して結び付ける」以外にはできません。 「関係性を示すキーとなる要素」をそれぞれのスキーマファイルに追加する 2つのXMLファイルを紐づけできるように、ここでは「関係性を示すキーとなる要素」として「注文番号(=orderNum)」を追加します。 追加する場所は子要素のなかでどこでも良いのですが、セル対応付けした時に解り易いように一番最後に来るようにしています。 ： 省略 <xsd:complexType name='PurchaseOrderType'> <xsd:sequence> <xsd:element name='shipTo' type='tns:USAddress'/> <xsd:element name='billTo' type='tns:USAddress'/> <xsd:element name='comment' type='xsd:string' minOccurs='0'/> <xsd:element name='orderNum' type='xsd:integer'/> </xsd:sequence> <xsd:attribute name='orderDate' type='xsd:date'/> <xsd:attribute name='confirmDate' type='xsd:date' use='required'/> </xsd:complexType> ： 省略 items要素以外(sample02x-1.xsd)のPurchaseOrderType型 orderNum要素をinteger型で追加しています。 ： 省略 <xsd:element name='item' minOccurs='0' maxOccurs='unbounded'> <xsd:complexType> <xsd:sequence> <xsd:element name='productName' type='xsd:string'/> <xsd:element name='quantity'> <xsd:simpleType> <xsd:restriction base='xsd:positiveInteger'> <xsd:minInclusive value='1'/> <xsd:maxExclusive value='100'/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name='USPrice' type='xsd:decimal'/> <xsd:element name='comment' type='xsd:string'/> <xsd:element name='shipDate' type='xsd:date' minOccurs='0'/> <xsd:element name='orderNum' type='xsd:integer'/> </xsd:sequence> <xsd:attribute name='partNum' type='tns:SKU'/> </xsd:complexType> </xsd:element> ： 省略 items要素(sample02x-2.xsd)のitem要素 orderNum要素をinteger型で追加しています。 ①② ①前章と同様に「purchaseOrders_対応付け1」をsheet1ワークシートにしてインポートします。 赤枠P列にorderNum要素が表示されます。 ②同様に「purchaseOrders_対応付け2」をsheet2ワークシートにしてインポートします。 赤枠G列にorderNum要素が表示されます。 ただし当然ではありますが、キーとなる項目はできたのですが、データが表示されていません。 データが入れば、エクスポートしたXMLファイルにもデータが反映されるので、それに基づいてコーディンクで処理する事ができそうです。 ではどのようにすればデータを入れられるのでしょうか? プログラムを作ってXMLファイルにキー項目「orderNum」を追加する レコード数が少ないような今回のXMLデータであれば、「sheet1のP列に上から順位1、2、3と入力し、Sheet2のG列には1、1、1、3と入力すれば良い」と簡単に解るのですが、やはり人手で対応した場合にはミスの発生をゼロにする事は難しい認識です。 従いまして、XMLファイルはテキストファイルなのでファイル全体を読み込んで加工処理するプログラムを作成した方が安心です。 なお実際のコーディング例につきましては、ここでご説明すると長くなってしまうので別記事にてExcelのVBAを使ったプログラムをご紹介したく存じます。 ここでは「プログラムで処理する際の考え方」についてご説明いたします。 スキーマファイルに合わせてキー項目を挿入するためには キー項目を挿入するに当たり、対象のXMLファイルを1行づつ読み込んで処理する事もできますが、ここではXMLファイルを一括読み込みをして処理する事にします。 処理のポイントは下記のようになります。 一括読み込みされたXMLデータをsplit関数を使って指定した目印タグで切り刻み配列にセットします。<purchaseOrder>タグに加するための目印タグ[1]と、その孫要素である<item>タグに追加するための目印タグ[2]の2種類の目印タグが必要になります。目印タグはsplit関数によって消去されてしまうため、コーディングで復活させる必要があります。目印タグ[1]の配列を処理するループのなかで、目印タグ[2]の配列を処理するループを回します。<purchaseOrder>タグに追加する<orderNum>の値と、その孫要素である<item>タブに追加する<orderNum>の値は同じ値にする必要があります。目印タグ[1]で切り刻んだ配列にセットされている最初と最後のパートのデータを漏らさないようにコーディングします。 目印タグを考える 目印タグは2種類必要ですが、まずは一つ目をご説明いたします。 一つ目の目印タグ スキーマファイルでorderNum要素を追加したのは、PurchaseOrderType型のcomment要素の後でしたが、XMLファイルの目印タグは”<items>”にします。(左図の挿入場所-1) この理由はcomment要素が省略可能になっていためです。 ただしitems要素の子要素のitemも省略可能な設定になっています。 でも大丈夫です。なぜなら親要素であるitems要素自身は省略される事がありません。 とは言いながら少し困った事があります。 というのはitem要素が省略されて<items>タグが空タグになる場合に、<items>タグの書き方が2パターン存在する事です。 上図で「パターンA」はitem要素がある場合であるのに対して、「パターンB」はitem要素が省略された空タグの場合です。 パターンA<items>…</items>パターンB<items/> Excelでエクスポートすると空タグはパターンBで出力されます。 このような場合、目印タグが完全なタグではなく一部分が欠けていたとしても問題なく目印タグとして機能します。 という事で2種類の目印タグの内の一つ目は”<items”という不完全なタグを使います。 2つ目の目印タグ もう一つはスキーマファイルでorderNum要素を追加したのは、item要素の子要素のshipDate要素の後でした。 上図「挿入場所その2」の位置になりますが、目印タグとしては”</item>”を使用します。 この理由も1つ目と同様にshipDate要素が省略可能に設定されているためです。 目印タグでsplitした時、配列に格納されるデータを把握する 次に目印タグでsplitするとどのような形でデータが別れるのかを確認します。 紫色の枠線で囲った箇所が①～③までの３ヶ所あります。これは目印タグ”<items”で切り刻んだ時にできるパートで、①が最初、③が最後のパートになり、②はitems要素の数分だけ存在します。なお左図ではたまたま③のパートの直前が空タグになっている形になっていますが、空タグではないケースもありますのでご注意ください。赤色の枠線で囲った箇所は①と②の2ヶ所あります。対象範囲としては紫色の枠線②と重なります。最初のパートと繰り返しのパートは形が同じになり、赤色の枠線②が最後のパートになります。赤色の枠線①のバートはitem要素の数だけ存在します。 以上で「プログラムで処理する際の考え方」の「前半」は終了です。「前半」と付けたのは最終的に2つに分割されたXMLファイルを合体するための「プログラムで処理する際の考え方」が必要になるためです。 この後は、まずは作成したコーディングでXMLファイルにキー項目を追加して、それぞれのセル範囲に対してインポートをしますが、やり方はいままでと同じですので画面は省略いたします。 キー項目を付けたXMLファイルをそれぞれエクスポートする いよいよ実際に「purchaseOrders_対応付け1」したセル範囲と「purchaseOrders_対応付け2」したセル範囲をXMLファイルとしてエクスポートします。 ①②③④ ①エクスポートするには、エクスポートする対象のセル範囲(テーブル)の任意のセルをクリックして置きます。 その後リボンメニューのエクスポートをクリックします。 ②セル範囲(テーブル)以外の場所にフォーカスが当たっていると対応付けを選択する画面が表示されますので、対象の対応付けを選んでOKボタンをクリックします。 ③保管場所を選びファイル名に保存する名前を入力します。ここではsample-02x-1e.xmlとしています。 ④同じように「purchaseOrders_対応付け2」もエクスポートします。ここではファイル名をsample-02x-2e.xmlとしています。 実際にエクスポートされるXMLファイルは次のようになります。 sample-02x-1e.xml <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns1:purchaseOrders xmlns:ns1="http://tempuri.org/PurchaseOrderSchema.xsd"> <ns1:purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <ns1:shipTo country="US"> <ns1:name>Corporate Sales Office: Boise</ns1:name> <ns1:street>401 W. Front Street, Suite 600</ns1:street> <ns1:city>Boise</ns1:city> <ns1:state>ID</ns1:state> <ns1:zip>83702</ns1:zip> </ns1:shipTo> <ns1:billTo country="US"> <ns1:name>Corporate Sales Office: Sacramento</ns1:name> <ns1:street>1415 L Street, Suite 200</ns1:street> <ns1:city>Sacramento</ns1:city> <ns1:state>CA</ns1:state> <ns1:zip>95814</ns1:zip> </ns1:billTo> <ns1:comment>Handle with care</ns1:comment> <ns1:orderNum>1</ns1:orderNum> </ns1:purchaseOrder> <ns1:purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <ns1:shipTo country="US"> <ns1:name>Corporate Sales Office: Austin</ns1:name> <ns1:street>10900 Stonelake Boulevard, Suite 225</ns1:street> <ns1:city>Austin</ns1:city> <ns1:state>TX</ns1:state> <ns1:zip>78759</ns1:zip> </ns1:shipTo> <ns1:billTo country="US"> <ns1:name>Corporate Sales Office: Tulsa</ns1:name> <ns1:street>7633 E. 63rd Place, Suite 300</ns1:street> <ns1:city>Tulsa</ns1:city> <ns1:state>OK</ns1:state> <ns1:zip>74133</ns1:zip> </ns1:billTo> <ns1:orderNum>2</ns1:orderNum> </ns1:purchaseOrder> <ns1:purchaseOrder orderDate="2021-09-20" confirmDate="2021-09-21"> <ns1:shipTo country="US"> <ns1:name>Corporate Sales Office: Austin</ns1:name> <ns1:street>10900 Stonelake Boulevard, Suite 225</ns1:street> <ns1:city>Austin</ns1:city> <ns1:state>TX</ns1:state> <ns1:zip>78759</ns1:zip> </ns1:shipTo> <ns1:billTo country="US"> <ns1:name>Corporate Sales Office: Tulsa</ns1:name> <ns1:street>7633 E. 63rd Place, Suite 300</ns1:street> <ns1:city>Tulsa</ns1:city> <ns1:state>OK</ns1:state> <ns1:zip>74133</ns1:zip> </ns1:billTo> <ns1:comment>Handle with care</ns1:comment> <ns1:orderNum>3</ns1:orderNum> </ns1:purchaseOrder> </ns1:purchaseOrders> sample-02x-2e.xml <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns1:purchaseOrders xmlns:ns1="http://tempuri.org/PurchaseOrderSchema.xsd"> <ns1:purchaseOrder> <ns1:items> <ns1:item partNum="001abc"> <ns1:productName>keyboard</ns1:productName> <ns1:quantity>3</ns1:quantity> <ns1:USPrice>11.1</ns1:USPrice> <ns1:comment>Buletooth</ns1:comment> <ns1:shipDate>2021-09-23</ns1:shipDate> <ns1:orderNum>1</ns1:orderNum> </ns1:item> <ns1:item partNum="101acf"> <ns1:productName>mouse</ns1:productName> <ns1:quantity>5</ns1:quantity> <ns1:USPrice>10.9</ns1:USPrice> <ns1:comment>Wireless</ns1:comment> <ns1:shipDate>2021-09-23</ns1:shipDate> <ns1:orderNum>1</ns1:orderNum> </ns1:item> <ns1:item partNum="201ffs"> <ns1:productName>pen</ns1:productName> <ns1:quantity>1</ns1:quantity> <ns1:USPrice>20.1</ns1:USPrice> <ns1:comment/> <ns1:shipDate>2021-09-23</ns1:shipDate> <ns1:orderNum>1</ns1:orderNum> </ns1:item> <ns1:item partNum="001abd"> <ns1:productName>keyboard1</ns1:productName> <ns1:quantity>1</ns1:quantity> <ns1:USPrice>11.11</ns1:USPrice> <ns1:comment>Buletooth</ns1:comment> <ns1:shipDate>2021-09-23</ns1:shipDate> <ns1:orderNum>3</ns1:orderNum> </ns1:item> </ns1:items> </ns1:purchaseOrder> </ns1:purchaseOrders> Excelでエクスポートした時の特徴は書き出し(エクスポート)の第1回でまとめていますので、ご参照いただければ幸いです。 キー項目を使って2つのXMLファイルを1つにまとめるプログラムを作る いよいよ最後の工程になります。 前回同様それぞれのXMLファイルを一括読み込みして処理をしますが、「プログラムで処理する際の考え方」のポイントは下記のようになります。 キー項目の値が「1から最後まで」次の処理を繰り返します。sample-02x-1e.xml(前章左側)をキー項目”<ns1:orderNum>X</ns1:orderNum>”(Xは1から最後までの数字)でsplitします。続けてsample-02x-2e.xml(前章右側)も同じキー項目でsplitします。splitした最初のパートからitem要素を取り出すための目印タグとして”<ns1:items>”を使用します。初回はファイル全体を対象にsplitしますが、次にsplitするのは残余(splitした一番最後のパート)の部分です。残余の部分は”</item>”タグから始まるので(キー項目が”</item>”の前にセットされているため)、”</item>”タグを取り除く必要があります。items要素が空タグだった場合はキー項目は存在しないので、空タグであることを判断して処理します。 上記を実際のXMLファイルの構造に当てはめて補足いたします。 sample-02x-1e.xml sample-02x-2e.xml sample-02x-1e.xml split関数によりキー項目で分割されると①～③のパートに分かれます。 ①は最初のパートで③が最後のパートになります。 ②はpurchaseOrder要素の数に基づき繰り返します。 sample-02x-2e.xml items要素が一つにまとまっているために、分割した時に、”<ns1:items>”と”</ns1:items>”のタグを補ってあげる必要があります。 最初のパート①はitem要素以外の項目があるので、”<ns1:items>”でsplitして分離します。 item要素の数に基づき②は繰り返すのですが、キー項目が変わった時は、「残余」を対象にsplitします。 その場合は①’の形になり、キー項目が変わった時は”</ns1:item>”を取り除く必要があります。 従いまして、キー項目が変わった時の最後で”<ns1:item”タグを補完してあげる必要があります。 最後のパートにつきましては、すでに補完されている項目とsample-02x-1e.xmlの最後のパートになるので、使用しない事になります。 以上で「プログラムで処理する際の考え方」の「後半」は終了になります。 合体されたXMLファイルで名前空間の接頭辞を書き換えるには ルート要素での名前空間の接頭辞の宣言に加えて、各要素のタグにもExcelが決めた接頭辞が付いています。 今回ご説明した中でも、Excelが決めた接頭辞をそのまま付けた形にしています。 このExcelが決めた接頭辞を変更したい時は、それぞれのXMLファイルを一括読み込みした時に、replaceしてしまうのが良い認識です。 それに合わせてコーディングのなかに登場するタグに付ける接頭辞も変更します。 まとめ unboundedの「リストのリスト」を解消する方法について、ご紹介してきました。 方法を箇条書きでまとめると次のようになります。 スキーマファイルをmaxOccurs属性を持つ要素が別になるように2つに分ける。sample02x-1.xsdとsample02x-2.xsdそれぞれのスキーマファイルにキー項目を追加します。orderNum要素コーディンクでXMLファイルにキー項目を値を入れて追加します。スキーマファイルを「XMLの対応付け」に追加しセルに対応付ける。purchaseOrders_対応付け1とpurchaseOrders_対応付け2XMLファイルをそれぞれインポートする。sample-02x.xmlXMLファイルをそれぞれエクスポートする。sample-02x-1e.xmlとsample-02x-2e.xml2つのXMLファイルをコーディンクで1つにまとめる。 「誰がそんなに面倒臭い事をするんですか?」と言われそうですが、この考え方を応用すれば「リストのリストのリスト」でも同じように対応する事ができます。 ただし… 対応付けるセル範囲が3つになるので処理する人間も大変になりますし、コーディングも新たなキー項目が必要になるので複雑化します… 願わくば、なるぺく早くこの「リストのリスト」のエラーが解消される事を望みます。 以上最後までご一読いただき誠にありがとうございました。
