＠IT：ExcelデータをOracleに格納する裏技（3/3）

［DBプログラミング特別企画］　3/3

ExcelデータをOracleに格納する裏技

日本オラクル
宮本尚志
2005/5/18

■Excel 2003でXML化してOracleに格納

　Excel 2003では、マクロを使わなくてもデータをXML形式で保存できます。その代わり、XMLスキーマ（XML文書の構造を定義する文書）が必要になります。

　また、Oracleのバージョンが10.1.0.1.0以降であれば、Oracleデータベース側で設定作業を行うことで、XML DBリポジトリに格納する際にデータをユーザー表に格納することも可能です（設定作業をしない場合、内部的な表に格納されます）。そうすれば、SQLから更新や集計などが簡単に行えるようになります。ここではユーザー表に格納する手順を解説します。

　ユーザー表にExcel 2003からXMLを格納するための手順は、以下のようになります。

XMLスキーマの作成
OracleデータベースにXMLスキーマを登録
XML DBリポジトリの設定を変更
ExcelシートにXMLスキーマをインポート
ExcelからデータをXML形式で保存

XMLスキーマの作成

　XMLスキーマは、XML文書の構造定義に当たります。XML文書の中に出現する要素・属性やその出現順序、データ型などを定義します。XMLスキーマを用意することで、アプリケーションから扱いやすいようにXML文書のフォーマットを決めることができます。今回は以下のXMLスキーマを使用します。

<?xml version="1.0" encoding="Shift-JIS"?>

<xs:schema

 xmlns:xs="http://www.w3.org/2001/XMLSchema"

 xmlns:xdb="http://xmlns.oracle.com/xdb"

 xmlns="http://www.oracle.co.jp/expense"

 targetNamespace="http://www.oracle.co.jp/expense"

 elementFormDefault="qualified">

  <xs:element name="経費精算書" xdb:defaultTable="経費申請表">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="申請番号" type="xs:string"/>

        <xs:element name="状態" type="xs:string"/>

        <xs:element name="承認者承認" type="xs:string"/>

        <xs:element name="経理承認" type="xs:string"/>

        <xs:element name="社員番号" type="xs:integer"/>

        <xs:element name="氏名" type="xs:string"/>

        <xs:element name="経費負担部署" type="xs:string"/>

        <xs:element name="申請日" type="xs:date"/>

        <xs:element name="承認者" type="xs:string"/>

        <xs:element name="申請内容" type="xs:string"/>

        <xs:element name="経費詳細">

          <xs:complexType>

            <xs:sequence
maxOccurs="unbounded">

              <xs:element
name="経費">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element
name="日付" type="xs:date"/>

                    <xs:element
name="経費種類" type="xs:string"/>

                    <xs:element
name="詳細" type="xs:string"/>

                    <xs:element
name="金額" type="xs:integer"/>

                  </xs:sequence>

                </xs:complexType>

              </xs:element>

            </xs:sequence>

          </xs:complexType>

        </xs:element>

        <xs:element name="合計" type="xs:integer"/>

     </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

リスト4　XMLスキーマの例（keihi2.xsd）

　XMLスキーマを作成する方法はいくつかありますが、Oracle JDeveloperなどのツールを使えば、GUIでXMLスキーマを作成できます。Oracle JDeveloperおよびXMLスキーマの詳細については、下記の情報を参照してください。

Oracle JDeveloper 10g（OTNの情報サイト）
SEのためのXML Schema入門（＠ITの連載記事）

OracleデータベースにXMLスキーマを登録

　OracleデータベースはXMLスキーマを解釈する「XMLスキーマ・プロセッサ」と呼ばれる仕組みを内蔵しており、OracleデータベースにXMLスキーマが登録されると、そのXMLスキーマに合わせたユーザー表が自動的に作成されます。そのXMLスキーマに基づくXMLファイルがXML DBリポジトリに保存されると、データは作成されたユーザー表に自動的に格納されるのです。

　XMLスキーマの登録は以下のようにして行います。

declare

  xsddoc clob:='<xs:schema xmlns

                ……
XMLスキーマを記述 ……

                </xs:schema>';

begin 

  dbms_xmlschema.registerSchema(

      '経費申請表.xsd',  --XMLスキーマをDB内で識別する識別子

      xsddoc, True

                                );

end;

/

リスト5　XMLスキーマの登録

　この処理により、XMLスキーマがOracleデータベースに登録され、自動的に表が作成されます。作成される表の名前は、XMLスキーマの中で指定することが可能です。今回使用するXMLスキーマの「xdb:defaultTable="経費申請表"」という個所が表名の指定に当たり、“経費申請表”という名前で表が作成されることになります。表名を指定しない場合は、ルート要素名を基に自動的に表名が生成されます。この表の構造は、XMLType型の列を1つだけ持つXMLType表と呼ばれるものになります。

XML DBリポジトリの設定を変更

　Oracle Database 10gで、Excel 2003からXMLファイルをユーザー表に保存する場合は、さらにOracleデータベースの設定を一部変更する必要があります。設定変更は、XML DBリポジトリのルートフォルダにある「xdbconfig.xml」を変更して行います。XML DBリポジトリへはWebDAVでアクセスできるので、Word 2000などから直接「xdbconfig.xml」ファイルを編集できます。Wordの「ファイル」→「開く」と選択し、現れたボックスの「ファイル名」欄に

http://hostname:8080/xdbconfig.xml

と入力します（hostnameはOracleサーバのホスト名またはIPアドレス、ポート番号はデフォルトの場合）。ユーザー認証のボックスには、データベースのsystemユーザーのユーザー名／パスワードを入力します。「xdbconfig.xml」が開いたら、先頭と末尾付近に以下のように赤字部分を追記します（先頭部分は追記内容に日本語が含まれる場合のみ）。

<?xml version="1.0" encoding="Shift-JIS"?>

<xdbconfig xmlns= ……

        …… 中略 ……

   </protocolconfig>

   <schemaLocation-mappings>

     <schemaLocation-mapping>

       <namespace>名前空間</namespace> 

       <element>ルート要素名</element> 

       <schemaURL>XMLスキーマ登録時に指定した識別子</schemaURL>

     </schemaLocation-mapping> 

   </schemaLocation-mappings>

   <xdbcore-xobmem-bound>1024</xdbcore-xobmem-bound>

   <xdbcore-loadableunit-size>16</xdbcore-loadableunit-size>

   </sysconfig>

</xdbconfig>

リスト6　xdbconfig.xmlの変更

　今回の例では、名前空間が「http://www.oracle.co.jp/expense」、ルート要素名は「経費精算書」、XMLスキーマ登録時の識別子は「経費申請表.xsd」となります。追記後、Word上で上書き保存すれば、Oracleデータベースの設定が変更されたことになります。

ExcelシートにXMLスキーマをインポート

　次はクライアント側の設定です。まずExcelのシートにXMLスキーマを対応付けます。Excel 2003のメニューから、「データ」→「XML」→「XMLソース」と選択します。シートの右側に「XMLソース」ウィンドウが表示されます（図3）。

図3　Excel 2003のシートにXMLスキーマを対応付ける（画像をクリックすると拡大します）

　ここで「XMLの対応付け」をクリックし、新しく開く「XMLの対応付け」ウィンドウで「追加」をクリックします。リスト4のXMLスキーマのファイルを選択すると、「XMLの対応付け」ウィンドウにXMLスキーマの情報が表示されます（図4）。

図4　「XMLの対応付け」ウィンドウ

　「OK」をクリックすると、XMLスキーマで定義されているXML構造が「XMLソース」ウィンドウに表示されます（図5）。

図5　「XMLソース」ウィンドウに表示されたXML構造（画像をクリックすると拡大します）

　この状態で、右側に表示されている要素をドラッグ＆ドロップで該当するセルに重ねることで、要素とセルをマッピングさせることができます。これにより、Excelシート内のデータをXML形式で保存できるようになります。マッピングさせた状態で「.xsl」ファイルまたは「.xlt」ファイルとして保存すれば、そのファイルを配布するだけで誰でもXML形式で保存ができるようになります。

　XMLスキーマ内で複数出現することが許されている要素に関しては、Excelシート上で自動的に入力行を増やすこともできます。今回の例では、経費詳細の表にカーソルを合わせると、自動的に行が1つ増え複数行を入力できるようになります。

　また、セルのデータ型をXMLスキーマのデータ型と合わせることができますので、データ型のチェックをExcel側でも行うことができます。

ExcelからデータをXML形式で保存

　すべての要素を対応付け終えたら、データを入力してXMLファイルとして保存できるかどうか確認しましょう。XMLファイルを生成するには、「名前を付けて保存」で、「ファイルの種類」を「XMLデータ（*.xml）」にして保存するか、「データ」→「XML」→「エクスポート」を選択します。今回例に挙げたXMLスキーマを使った場合は、以下のようなXMLファイルが生成されます。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ns1:経費申請 xmlns:ns1="http://www.oracle.co.jp/expense">

  <ns1:申請番号>1234-20050401123045</ns1:申請番号>

    …… 中略 ……

</ns1:経費申請>

リスト7　Excel 2003から生成されるXMLの例

　Excel 2000でマクロを使った場合と違い、すべての要素名に「ns1:」という接頭辞が付いています。また、ルート要素「経費申請」に「xmlns:ns1=……」という属性が付加され、各要素がどの名前空間に属しているかが指定されています。xdbconfig.xmlの設定変更により、名前空間が分かればその名前空間を定義するXMLスキーマも分かるため、Oracle XML DBリポジトリにこのXMLファイルを保存すると、自動的にXMLスキーマ登録時に作成された表（この例では経費申請表）にデータを格納できるようになります。

　XML DBリポジトリにExcel 2003からXMLファイルを保存するには、「名前を付けて保存」で「ファイル名」を以下のように指定します（hostnameはOracleサーバのホスト名またはIPアドレス、ポート番号はデフォルトの場合）。

http://hostname:8080/public/filename.xml

　「xdbconfig.xml」をWordで編集したときと同様、ユーザー認証のウィンドウが出てきますが、ここではXMLスキーマを登録したDBユーザーのユーザー名・パスワードを入力します。これでXML DBリポジトリにXMLファイルを保存できました。実際にXMLスキーマ登録時の表に格納されたかどうか確認してみましょう。

SQL> set long 1000000

SQL> select * from "経費申請表";



SYS_NC_ROWINFO$

-------------------------------------------

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ns1:経費精算書 xmlns:ns1="http://www.oracle.co.jp/expense">



　…… 以下略 ……

リスト8　ユーザー表からの検索

　単にXML文書全体を取り出すだけでなく、XML文書の特定要素内容のみを抽出することも可能です。

SQL> select extractValue(object_value, '/経費精算書/合計')

 2 > from "経費申請表";



EXTRACTVALUE(OBJECT_VALUE,'/経費精算書/合計')

---------------------------------------------

                                        31490

リスト9　XPathを使った検索

　ここでは詳しく書きませんが、このようにユーザー表にXMLとして格納することで非常に多様な処理を行えるようになります。Excel 2003とOracleデータベースさえあれば簡単に試すことができますので、ぜひ挑戦してください。なお、OracleデータベースのXML機能の詳細については、以下のOTN Japanの情報ページが役に立つでしょう。（完）

「Oracle XML DB開発者ガイド」
「Oracle XML DB」

3/3

Index
［DBプログラミング特別企画］ ExcelデータをOracleに格納する裏技
	Page 1 ・ExcelとOracleの相性はかなり良好・ExcelデータをXMLに変換してOracleに格納するメリット・事前準備とExcelデータをXML化する2つの方法
	Page 2 ・VBAでExcelデータをXML化してOracleに格納する
	Page 3 ・Excel 2003でXML化してOracleに格納

ExcelデータをOracleに格納する裏技

Database Expert フォーラム新着記事

Oracleライセンス「SE2」検証　CPUスレッド数制限はどんな仕組みで制御されるのか （2017/7/26）
　データベース管理システムの運用でトラブルが発生したらどうするか。DBサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は、Oracle SE2の「CPUスレッド数制限」がどんな仕組みで行われるのかを検証します
ドメイン参加後、SQL Serverが起動しなくなった （2017/7/24）
　本連載では、「SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は、「ドメイン参加後にSQL Serverが起動しなくなった場合の対処方法」を解説します
さらに高度なSQL実行計画の取得」のために理解しておくべきこと （2017/7/21）
　日本オラクルのデータベーススペシャリストが「DBAがすぐ実践できる即効テクニック」を紹介する本連載。今回は「より高度なSQL実行計画を取得するために、理解しておいてほしいこと」を解説します
データベースセキュリティが「各種ガイドライン」に記載され始めている事実 （2017/7/20）
　本連載では、「データベースセキュリティに必要な対策」を学び、DBMSでの「具体的な実装方法」や「Tips」などを紹介していきます。今回は、「各種ガイドラインが示すコンプライアンス要件に、データベースのセキュリティはどのように記載されているのか」を解説します

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

注目のテーマ

Database Expert 記事ランキング

本日月間

＠IT

注目のテーマ

Database Expert 記事ランキング

転職／派遣情報を探す

年収６００万円！エンジニア向けの求人満載。転職希望者必見

エンジニアの転職なら【転職サーチ】