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

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

https://qiita.com/noobow/items/25efab18a2836abb668d

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

https://gist.github.com/noobow34/2d99a41f0478b06927403f3862d27985

```powershell
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に作ってもらいました。
```sql
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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149348/e8b3a18c-6d0b-4fe1-aa1d-8d7a9a9a77d9.png)
```powershell
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
(元のSQL) FOR XML PATH('ROW'),ROOT('ROWSET')
```


```powershell
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だけの出力になります。
```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型にキャストできる状態になりましたので以下のように変数に格納できます。
```powershell
$xml = [xml]($execSql | sqlcmd -S server -U user -P pass -h -1 -y 8000  | ForEach-Object{ $_.Trim() })
```

## XML変数の扱い方
[Oracle版](https://qiita.com/noobow/items/25efab18a2836abb668d#xml%E5%A4%89%E6%95%B0%E3%81%AE%E6%89%B1%E3%81%84%E6%96%B9)と同じです。