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

この記事は、先日書いた以下の記事のSQL Server(sqlcmdコマンド)版です。

まずは結果から

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

PS C:\> .\Get-SqlcmdXmlData.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をChatGPTに作ってもらいました。

WITH Numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SEQ
    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS V(N)
)
SELECT SEQ, SEQ + 1 AS SEQ2
FROM Numbers

PS C:\> @"
>> WITH Numbers AS (
>>     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SEQ
>>     FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS V(N)
>> )
>> SELECT SEQ, SEQ + 1 AS SEQ2
>> FROM Numbers
>> "@ | sqlcmd -S localhost\sqlexpress
SEQ                  SEQ2
-------------------- --------------------
                   1                    2
                   2                    3
                   3                    4
                   4                    5
                   5                    6
                   6                    7
                   7                    8
                   8                    9
                   9                   10
                  10                   11

(10 行処理されました)

sqlcmdの出力をXMLにする

SQL Serverでは通常のSQLの最後にFOR XML xxxをつけてやると結果がXMLになります。FOR XMLの後は色々な書き方がありますがFOR XML PATH('ROW'),ROOT('ROWSET')とすると今回扱いやすいXMLになります。

(元のSQL) FOR XML PATH('ROW'),ROOT('ROWSET')

PS C:\Users\skywi> @"
>> WITH Numbers AS (
>>     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SEQ
>>     FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS V(N)
>> )
>> SELECT SEQ, SEQ + 1 AS SEQ2
>> FROM Numbers FOR XML PATH('ROW'),ROOT('ROWSET');
>> "@ | sqlcmd -S server -U user -P pass
XML_F52E2B61-18A1-11d1-B105-00805F49916B


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<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</

(1 行処理されました)

例によってこれだけではまだ問題がありますので以下のようにします。

• SQLの前にset nocount on;をつける→(1行処理されました)が出なくなります
• オプションに-h -1 -y 8000をつける→列名が出なくなります、XMLで出力できるバイト数を最大値にします
• -y 8000をつけると最大バイト数に満たない部分は空白が出てしまうようなのでトリムする

これらを合わせると、以下のようにXMLだけの出力になります。

PS C:\> @"
>> set nocount on;
>> WITH Numbers AS (
>>     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SEQ
>>     FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS V(N)
>> )
>> SELECT SEQ, SEQ + 1 AS SEQ2
>> FROM Numbers FOR XML PATH('ROW'),ROOT('ROWSET');
>> "@ | sqlcmd -S server -U user -P pass -h -1 -y 8000 | ForEach-Object{ $_.Trim() }
<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>

これでPowerShellのXML型にキャストできる状態になりましたので以下のように変数に格納できます。

$xml = [xml]($execSql | sqlcmd -S server -U user -P pass -h -1 -y 8000  | ForEach-Object{ $_.Trim() })

XML変数の扱い方

Oracle版と同じです。