日暮らしSEの日々考慮　〜名古屋でオフショア そしてセレブへ？〜

公開しちゃいますXMLデータからリレーショナル表の形式に変換する方法♪

最近ヒッキーになってるXMLDB君の情報です(^_^;)
OTNの掲示板の方にいったん記載したのですが、自分自身が忘れないようにする為にも
同内容をコミカルに？こちらのほうに記載します。　

そもそもなんでそんなことすんねんとツッコまれそうですが、
開発においてORマッパーを使用しており、RDBもXMLDBも混じってますので、
開発効率の為にもそいつを巧く利用できるようにするためにリレーショナルの表形式にする必要があったのです。

今回はXMLTABLE関数というものを使います。
この関数は10gR2かららしいのですが、SQL標準（2003？だったかな…）になっているようで、
Oracle君以外にも他の子にも仲良くできるはずです♪
（ちょっとSQLリファレンス手元から消えたので確認はしてください(^_^;)）

そこでサンプルとして下記のXMLデータを用意します。
これは以前にも記載したサンプルを元に拡張してXMLスキーマ定義を
XMLDBに定義して、Insertしてある2つのXMLデータです。
ちょっと名前空間に疎くっておぉ〜という声は聞こえてこないかもしれませんが…

--XMLTYPE表に挿入されているもの。(同用のXML文書2つ)
<?xml version="1.0" encoding="SHIFT_JIS"?>
<emp:empInfo xmlns:emp="http://otnxml.oracle.co.jp/empInfo.xsd"
　xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
　xsi:schemaLocation="http://otnxml.oracle.co.jp/empInfo.xsd empInfo.xsd">
  <emp:employee emp:id="778200" emp:type="fulltime">
    <emp:personalInfo>
      <emp:name>CLARK</emp:name>
      <emp:localName>クラーク</emp:localName>
      <emp:sexuality>Male</emp:sexuality>
      <emp:job>MANAGER</emp:job>
      <emp:manager>783900</emp:manager>
      <emp:hireDate>19810609</emp:hireDate>
      <emp:salary>2450</emp:salary>
    </emp:personalInfo>
    <emp:depts>
      <emp:dept emp:status="now">
        <emp:deptName>ACCOUNTING</emp:deptName>
        <emp:location>ニューヨーク</emp:location>
      </emp:dept>
      <emp:dept emp:status="last">
        <emp:deptName>サポートサービス本部</emp:deptName>
        <emp:location>用賀</emp:location>
      </emp:dept>
      <emp:dept xmlns:emp="http://otnxml.oracle.co.jp/empInfo.xsd"　emp:status="add">
        <emp:deptName>あいどら</emp:deptName>
        <emp:location>ありど</emp:location>
      </emp:dept>
    </emp:depts>
  </emp:employee>
</emp:empInfo>

<?xml version="1.0" encoding="SHIFT_JIS"?>
<emp:empInfo xmlns:emp="http://otnxml.oracle.co.jp/empInfo.xsd"
　xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
　xsi:schemaLocation="http://otnxml.oracle.co.jp/empInfo.xsd empInfo.xsd">
  <emp:employee emp:id="784400" emp:type="part">
    <emp:personalInfo>
      <emp:name>TURNER</emp:name>
      <emp:localName>ターナー</emp:localName>
      <emp:sexuality>Male</emp:sexuality>
      <emp:job>SALESMAN</emp:job>
      <emp:manager>769800</emp:manager>
      <emp:hireDate>19810908</emp:hireDate>
      <emp:salary>1500</emp:salary>
    </emp:personalInfo>
    <emp:depts>
      <emp:dept emp:status="now">
        <emp:deptName>SALES</emp:deptName>
        <emp:location>シカゴ</emp:location>
      </emp:dept>
      <emp:dept emp:status="last">
        <emp:deptName>サポートサービス本部</emp:deptName>
        <emp:location>用賀</emp:location>
      </emp:dept>
      <emp:dept xmlns:emp="http://otnxml.oracle.co.jp/empInfo.xsd" emp:status="add">
        <emp:deptName>あいどら</emp:deptName>
        <emp:location>ありど</emp:location>
      </emp:dept>
    </emp:depts>
  </emp:employee>
</emp:empInfo>

に対して

--リレーショナル表形式への変換
SELECT
    enpinfo.name,
    enpinfo.local_name,
    sexuality,
    job
FROM empInfo_xml,
    XMLTABLE('/empInfo'
        passing object_value
        COLUMNS
        name varchar2(40) path '/empInfo/employee/personalInfo/name',
        local_name varchar2(40) path '/empInfo/employee/personalInfo/localName',
        sexuality varchar2(40) path '/empInfo/employee/personalInfo/sexuality',
        job varchar2(40) path '/empInfo/employee/personalInfo/job'
) enpinfo

をすると、

詳しくは、10gR2の「Oracle XML DB開発者ガイド」および「SQLリファレンス」を
参考にしてください。

NAME 　　 LOCAL_NAME   SEXUALITY     JOB
-------------------------------------------
TURNER   ターナー          Male 　         　SALESMAN
KING 　　   キング 　　        Male 　　         PRESIDENT

といった結果が得られます♪この時に各カラムの型を指定してあげないとしかられますので、
上記みたいないい加減指定ではなくってちゃんとしてあげてください☆

ちなみに上記はXMLType表からの変換ですので、XMLType列から生成する場合には、
「passing object_value」部分が「passing XML_TYPE列の列名」に
なってくるはずです_(_^_)_

また簡易的に「XMLTABLE('/empInfo' 」としてますが、
リファレンスによると<strong>XQuery</strong>による生成ができるようなので、柔軟に情報取得はできるかと思われます。