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

前提条件

• SQLServer (2017/Azure)
• SQLServerManagementStudio v17.3

文字列と日付型の変換

日時の表記形式一覧

--暗黙の変換(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

実行結果

月末日・月初日の取得

--月初日/月末日の取得
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 [翌月月末];

実行結果

文字列とJSON型の変換

Microsoft Azure の Cognitive Service「Text Analytics API」をサンプルにしてみる。

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

基本的な使い方

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

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

実行結果

JSONの配列取得１

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

実行結果

JSONの配列取得２

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

実行結果

文字列と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 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://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)

実行結果