Oracle(sqlplusコマンド)の実行結果をXMLにしてPowerShellで扱う

はじめに

最近、PowerShellおじさんに俺はなる！ ということで突然PowerShellに目覚、その使い勝手の良さにすっかりハマっています。もともと私は.NET系の開発者だということもあり、PowerShellとの親和性が高いと感じています。色々な考え方・できることが慣れ親しんだC#に近いのと、コマンドの結果がオブジェクトで構造化されているのもいいですね。
ちゃんと勉強してみるかと、PowerShell100本ノックも始めましたのでよかったらご覧ください。

そんな中で「こんなことできるかな？」と調べてやってみたらうまくいって、まだ同じことをやってる記事がなさそうで、かつ結構便利だなと思うスクリプトができたので紹介します。

やりたいこと

仕事でこんなスクリプトが欲しくなりました。

• SELECT COUNT(*)のSQLを2つ実行して、結果が一致（件数が一致）するか確認する（一致するまで繰り返す）
• SELECT COUNT(*)の結果が0になるか確認する（0になるまで繰り返す）

DBMSはOracleなのでSQL*PlusでSQLを実行して結果を得るとして、その結果（SELECT結果）をうまいことPowerShellに持ち込む必要があります。これをどうすればよいのか考えました。
この記事ではSQLを実行して結果をPowerShellで扱えるようにするところまでを説明します。もともと私がやりたかった「SQLを2つ実行して結果が一致するか」とか「一致するまで繰り返す」とかは業務に特化した話なので割愛します。

まずは結果から

サンプルスクリプトは以下のGistに格納しました。
https://gist.github.com/noobow34/57eb32e4de0830c9f2ba0d7cae3fe12a

実行結果

PS C:\> .\Get-SQLPlusXmlData.ps1
-----------行を指定して使用する-----------

SEQ SEQ2
--- ----
2   3
-----------行と列を指定して使用する-----------
3
-----------ループで処理する場合-----------
1:2
2:3
3:4
4:5
5:6
6:7
7:8
8:9
9:10
10:11

やりかた

扱うデータ

Oracleがあればだれでも試せるように、テーブルがなくても結果が得られる以下のSQLを本記事では使用します。

SELECT LEVEL SEQ ,LEVEL+1 SEQ2 FROM DUAL CONNECT BY LEVEL <=10

このSQLの結果は以下のようになります。取得したデータの扱い方を説明しやすいように、複数行・複数列の結果となるようにしています。

SQL*Plusの出力をXMLにする

普通にSQL*Plusで実行すると結果はこんな感じになります。見た目が表になっているだけで構造化されているデータではなく、余計なメッセージも大量にあるのでのでこのままではPowerShellに持ち込んで扱うのは難しいことがわかります。

PS C:\> "SELECT LEVEL SEQ ,LEVEL+1 SEQ2 FROM DUAL CONNECT BY LEVEL <=10;" | sqlplus user/pass@server/service

SQL*Plus: Release 21.0.0.0.0 - Production on 金 2月 7 07:08:47 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

最終正常ログイン時間: 金 2月  07 2025 07:08:28 +09:00


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SQL>
       SEQ       SEQ2
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5          6
         6          7
         7          8
         8          9
         9         10
        10         11

10行が選択されました。

SQL> Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。

そこで結果をXMLにすることはできないのかと考えたところ、dbms_xmlgen.getxmlという関数を使うと結果がXMLで得られることがわかりました。以下の記事を参考にさせていただきました。

いくつかオプションを設定していますが意味は以下の通りです。

• set pages 0:ページング無し、これを設定しないとヘッダとして列名も表示されてしまいます。
• set long 2000000000:XMLの最大バイト数の設定です。2000000000が最大値です。結果がこれ以上になる場合は切れてしまいますのでご注意ください。
• sqlplus -s:-sがサイレントモードを意味し、出力が結果のみになります。これを指定しない場合、SQL*Plusの各種メッセージが表示されます。

まずはオプション無しで実行した場合を見てみましょう。XML以外の各種メッセージが表示されset longのデフォルトが小さいのでXMLが途中で切れているのも致命的ですね。

PS C:\> "select dbms_xmlgen.getxml('SELECT LEVEL SEQ ,LEVEL+1 SEQ2 FROM DUAL CONNECT BY LEVEL <=10') from dual;" | sqlplus user/pass@server/service

SQL*Plus: Release 21.0.0.0.0 - Production on 金 2月 7 07:13:30 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

最終正常ログイン時間: 金 2月  07 2025 07:13:24 +09:00


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SQL>
DBMS_XMLGEN.GETXML('SELECTLEVELSEQ,LEVEL+1SEQ2FROMDUALCONNECTBYLEVEL<=10')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <SEQ>1</SEQ>
  <SEQ2>2</SEQ2>
 </ROW>
 <


SQL> Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。

オプションありで実行してみます。以下のような純粋にXMLのみの結果が、SQL*Plusの実行結果として得られるようになります。

PS C:\> "set pages 0 `n set long 2000000000 `n set trimspool on `n select dbms_xmlgen.getxml('SELECT LEVEL SEQ ,LEVEL+1 SEQ2 FROM DUAL CONNECT BY LEVEL <=10') from dual;" | sqlplus -s user/pass@server/service
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <SEQ>1</SEQ>
  <SEQ2>2</SEQ2>
 </ROW>
 <ROW>
  <SEQ>2</SEQ>
  <SEQ2>3</SEQ2>
 </ROW>
 <ROW>
  <SEQ>3</SEQ>
  <SEQ2>4</SEQ2>
 </ROW>
 <ROW>
  <SEQ>4</SEQ>
  <SEQ2>5</SEQ2>
 </ROW>
 <ROW>
  <SEQ>5</SEQ>
  <SEQ2>6</SEQ2>
 </ROW>
 <ROW>
  <SEQ>6</SEQ>
  <SEQ2>7</SEQ2>
 </ROW>
 <ROW>
  <SEQ>7</SEQ>
  <SEQ2>8</SEQ2>
 </ROW>
 <ROW>
  <SEQ>8</SEQ>
  <SEQ2>9</SEQ2>
 </ROW>
 <ROW>
  <SEQ>9</SEQ>
  <SEQ2>10</SEQ2>
 </ROW>
 <ROW>
  <SEQ>10</SEQ>
  <SEQ2>11</SEQ2>
 </ROW>
</ROWSET>

XMLをPowerShellの変数にする

XMLが出力できたのでこれをPowerShellの変数に格納して使いましょう。PowerShellではコマンドを右辺に書けば実行結果をそのまま変数に格納することができます。一旦ファイルに保存する必要がないので、ファイル名が被らないようにするにはどうしようとか考えなくていいので楽ですね。

$xml = [xml]($execSql | sqlplus -S user/psss@server/service)

[xml]をつけることで、XML型(System.Xml.XmlDocument)にキャストします。これで単なる文字列ではなくXMLとして扱うことができます。

XML変数の扱い方

PowerShellのXML変数では属性名をそのままプロパティとして使うことができます。とっても楽ですね。また、繰り返しの属性は配列として扱うことができます。
ということで以下のようにSQLの実行結果を扱うことができます。

• 配列としてインデックスを指定して、行を指定

$xml.ROWSET.ROW[1]

• さらに列を指定

$xml.ROWSET.ROW[1].SEQ2

• 行をループさせて処理

foreach($row in $xml.ROWSET.ROW){
    $row.SEQ + ":" + $row.SEQ2
}

おわりに

SQL*Plusの実行結果をXMLにすることで、実質的にPowerShellでエンティティクラスとして扱うことができとても便利になりました。
SQLを実行するコマンドの結果をXMLにすることができれば、他のDBMSでも応用できます。ほかのDBMSのコマンドラインツール（mysql,psql,sqlite3,sqlcmd）でも同じことができないか調べて、できれば続報の記事を書きたいと思います。