[SQL SERVER] XMLをテーブル形式に変換するSQL

以下のXMLデータをテーブル形式に変換する方法を説明します。

<NewDataSet>
<row>
<Title>タイトル1</Title>
<Memo>内容1</Memo>
<UserID>test1</UserID>
<Count>1</Count>
</row>
<row>
<Title>タイトル2</Title>
<Memo>内容2</Memo>
<UserID>test2</UserID>
<Count>2</Count>
</row>
</NewDataSet>

一つ目方法！

このまま実行してみてください。

DECLARE @XmlData xml
SET @XmlData = '<NewDataSet><row><Title>タイトル1</Title><Memo>内容1</Memo><UserID>test1</UserID><Count>1</Count></row><row><Title>タイトル2</Title><Memo>内容2</Memo><UserID>test2</UserID><Count>2</Count></row></NewDataSet>'


DECLARE @IntDoc int

EXEC sp_xml_preparedocument @IntDoc OUTPUT, @XmlData

SELECT Title, Memo, UserID, Count
FROM OPENXML(@Intdoc, '/NewDataSet/row',2)
WITH (Title nvarchar(20), Memo nvarchar(20), UserID varchar(20), Count int)

EXEC sp_xml_removedocument @Intdoc

そして二つ目。

DECLARE @XmlData xml
SET @XmlData = '<NewDataSet><row><Title>タイトル1</Title><Memo>内容1</Memo><UserID>test1</UserID><Count>1</Count></row><row><Title>タイトル2</Title><Memo>内容2</Memo><UserID>test2</UserID><Count>2</Count></row></NewDataSet>'


SELECT Node.Data.value('(Title)[1]','nvarchar(20)') AS Title
, Node.Data.value('(Memo)[1]','nvarchar(20)') AS Memo
, Node.Data.value('(UserID)[1]','varchar(20)') AS UserID
, Node.Data.value('(Count)[1]','int') AS Count
FROM @XmlData.nodes('/NewDataSet/row') Node(Data)

両方同じ結果を得られます。

実行結果

一つ目は変数がXMLでもnvarchar, varcharでも使えます。

二つ目の方法は変数がXMLデータ型のみ使えます。