[SQLServer] 文字列→日付/JSON/XML変換クエリまとめ

## 前提条件
 - SQLServer (2017/Azure)
 - SQLServerManagementStudio v17.3

## 文字列と日付型の変換
### 日時の表記形式一覧
```sql
--暗黙の変換(nvarchar → datetime変換)
DECLARE @Date DATETIME = '2018/5/4 03:02:01';

--年月6桁yyyymm
DECLARE @yyyymm NVARCHAR(6) = LEFT(CONVERT(NVARCHAR, @Date, 112), 6)

--主な日時表記一覧(datetime → nvarchar変換)
SELECT   
    @Date AS 'GETDATE',
    @yyyymm as 'YYYYMM',
    CONVERT(NVARCHAR, @Date, 111) AS '111', -- yyyy/mm/dd
    CONVERT(NVARCHAR, @Date, 11)  AS '11',  -- yy/mm/dd
    CONVERT(NVARCHAR, @Date, 112) AS '112', -- yyyymmdd
    CONVERT(NVARCHAR, @Date, 12)  AS '12',  -- yymmdd
    CONVERT(NVARCHAR, @Date, 101) AS '101', -- mm/dd/yyyy
    CONVERT(NVARCHAR, @Date, 1)   AS '1',   -- mm/dd/yy
    CONVERT(NVARCHAR, @Date, 103) AS '103', -- dd/mm/yyyy
    CONVERT(NVARCHAR, @Date, 3)   AS '3',   -- dd/mm/yy
    CONVERT(NVARCHAR, @Date, 108) AS '108', -- HH:mi:ss
    CONVERT(NVARCHAR, @Date, 114) AS '114'; -- HH:mi:ss:mmm
```
#### 実行結果
<img width="1200" alt="実行結果
" src="https://qiita-image-store.s3.amazonaws.com/0/94824/ddc264b4-3bee-9489-1739-7f2d7316b6f0.png">

### 月末日・月初日の取得
```sql
--月初日/月末日の取得
DECLARE @Date DATETIME = '2018/5/4 03:02:01';
DECLARE @CONVERTCODE int = 111;
SELECT 
  CONVERT(NVARCHAR, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date) ,@CONVERTCODE)                       AS [当月月初],
  CONVERT(NVARCHAR, DATEADD(MONTH, -1, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date) ) ,@CONVERTCODE)  AS [前月月初],
  CONVERT(NVARCHAR, DATEADD(MONTH, 1, DATEADD(DAY, 1-DATEPART(DAY, @Date), @Date) ) ,@CONVERTCODE)   AS [翌月月初],
  CONVERT(NVARCHAR, DATEADD(MONTH, 1, DATEADD(DAY, 0-DATEPART(DAY, @Date), @Date) ) ,@CONVERTCODE)  AS [当月月末],
  CONVERT(NVARCHAR, DATEADD(MONTH, 0, DATEADD(DAY, 0-DATEPART(DAY, @Date), @Date) ) ,@CONVERTCODE)  AS [前月月末],
  CONVERT(NVARCHAR, DATEADD(MONTH, 2, DATEADD(DAY, 0-DATEPART(DAY, @Date), @Date) ) ,@CONVERTCODE)  AS [翌月月末];

```

#### 実行結果
<img width="640" alt="実行結果
" src="https://qiita-image-store.s3.amazonaws.com/0/94824/8b7e01b3-b035-c601-3663-ad2960473b6f.png">


## 文字列とJSON型の変換
Microsoft Azure の Cognitive Service「[Text Analytics API](https://azure.microsoft.com/ja-jp/services/cognitive-services/text-analytics/)」をサンプルにしてみる。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/94824/dbdeb626-3764-a604-b439-744ba6f32fa5.png)

※少々長いので、keyPhrasesのみ抜粋しています。

### 基本的な使い方
```sql
DECLARE @json nvarchar(max) = N'{
  "keyPhrases": {
    "documents": [
      {
        "id": "66de1852-6fed-4932-b16f-6adff004e934",
        "keyPhrases": [
          "共有",
          "知識",
          "記録",
          "知見",
          "ため",
          "ましょう ;)",
          "エンジニアリング",
          "サービス",
          "つい",
          "Qiita"
        ]
      }
    ],
    "errors": []
  }
}'

--基本的な使い方：OPENJSON
SELECT *
FROM OPENJSON(@json, N'$.keyPhrases')

```
#### 実行結果
<img width="512" alt="実行結果" src="https://qiita-image-store.s3.amazonaws.com/0/94824/af6a1fc9-e650-a473-9d2c-97ccb71a3995.png">

### JSONの配列取得１
```sql

--配列を含む場合はWITHが使える　※as jsonにするならnvarchar(max)でないとエラー
SELECT *
FROM OPENJSON(@json, N'$.keyPhrases.documents')
WITH (
  id nvarchar(200),
  keyPhrases nvarchar(max) as json
)

```

#### 実行結果
<img width="512" alt="実行結果" src="https://qiita-image-store.s3.amazonaws.com/0/94824/ee286d27-ce61-f728-7fdd-1363f9330501.png">



### JSONの配列取得２
```sql
--OPENJSONはJSON型に変換可能であればクエリを入れ子にもできる
SELECT *
FROM OPENJSON(
 ( SELECT [value] FROM OPENJSON(@json, N'$.keyPhrases.documents') ), N'$.keyPhrases'
)

```
#### 実行結果
<img width="240" alt="実行結果" src="https://qiita-image-store.s3.amazonaws.com/0/94824/ed38b5b2-235a-500b-f59c-900c03ddfcea.png">



## 文字列とXML型の変換

### 基本的な使い方：XML.nodes()
Wikipedia APIの記事本文取得をサンプルにしてみる。
取得URLはこちら
https://ja.wikipedia.org/w/api.php?format=xml&action=query&prop=revisions&titles=%E6%97%A5%E6%9C%AC%E8%AA%9E&rvprop=content

※注：本記事ではエスケープ処理を施していません。
　SQLServerのクエリで正常に動作しない「～」「'」などの文字がAPI取得結果に混入する可能性があります。

```xml
<?xml version="1.0"?>
<api batchcomplete="">
  <query>
    <pages>
      <page _idx="11" pageid="11" ns="0" title="日本語">
        <revisions>
          <rev contentformat="text/x-wiki" contentmodel="wikitext" xml:space="preserve">
{{wikt}}
{{Infobox Language
|name=日本語
...
[[Category:日本語族]]
{{Featured article}}
          </rev>
        </revisions>
      </page>
    </pages>
  </query>
</api>
```

```sql
--https://ja.wikipedia.org/w/api.php?format=xml&action=query&prop=revisions&titles=%E6%97%A5%E6%9C%AC%E8%AA%9E&rvprop=content
DECLARE @xml xml = N'<?xml version="1.0"?><api batchcomplete=""><query><pages><page _idx="11" pageid="11" ns="0" title="日本語"><revisions><rev contentformat="text/x-wiki" contentmodel="wikitext" xml:space="preserve">{{wikt}}{{Infobox Language|name=日本語...[[Category:日本語族]]{{Featured article}}</rev></revisions></page></pages></query></api>'

--https://docs.microsoft.com/ja-jp/sql/t-sql/xml/xml-data-type-methods?view=sql-server-2017
SELECT
    page.query('.') [query]
  , page.value('@pageid','int') as [page_id]
  , page.value('@ns','int') as [page_namespace]
  , page.value('@title','nvarchar(128)') as [page_title]
  , page.exist('@missing') as [is_page_missing]
  , page.value('(./revisions/rev)[1]/@contentformat','nvarchar(128)') as [rev_contentformat]
  , page.value('(./revisions/rev)[1]/@contentmodel','nvarchar(128)') as [rev_contentmodel]
  , page.value('(./revisions/rev)[1]/@xml:space','nvarchar(128)') as [rev_xmlspace]
  , page.value('(./revisions/rev/text())[1]','nvarchar(max)') as [page_text]
FROM @xml.nodes('/api/query/pages/page') as wikixml(page)
```


#### 実行結果
<img width="1024" alt="実行結果" src="https://qiita-image-store.s3.amazonaws.com/0/94824/51372c55-2aa6-9c29-7dc9-990d9221ec6e.png">