XMLファイルからテーブルへのINSERT

• Goal
  
  XMLファイルからテーブルの要素を抜き出して、INSERTしたい。
  SQLServer バージョン
  
  
  
• How
  
  xqueryを使って実施。
  
  
  

  	DECLARE @xml as XML
  	--temp Manual set
  	SET @xml = CAST('<Logs>
  	<LogDateEntry date="01/01/2017">
  	<Details time="05:16:26" Type="GET" subject="TEST1" details="This is test" value="50" target="SERVER1" />
  	<Details time="05:16:35" Type="PUT" subject="TESTSE" details="aseasdfasdfa" value="125" target="SERVER3" />
  	<Details time="05:25:24" Type="PUT" subject="ddddd" details="asdfasdfasdfsafdsafdasdfasfdsadfasdf" value="1524" target="SERVER1" />
  	<Details time="05:45:23" Type="GET" subject="asdfasdfasdfasdfasfdsafd" details="asdfasfdsafdasfdasdfasdfasdfasdfasdfasdfsa" value="12" target="SERVER1" />
  	</LogDateEntry>
  	</Logs>' as XML)
  	--Insert
  	--insert into TEST_LOG(DATE,TIME,alertType,subject,details,value,target,creation_date)
  	--select
  	select CONVERT(VARCHAR(50),TLOG.c.query('data(..//@date)')) as DATE
  	, CONVERT(VARCHAR(1000),TLOG.c.query('data(.//@time)')) as TIME
  	, CONVERT(VARCHAR(1000),TLOG.c.query('data(.//@Type)')) as AT
  	, CONVERT(VARCHAR(1000),TLOG.c.query('data(.//@subject)')) as SUB
  	, CONVERT(VARCHAR(1000),TLOG.c.query('data(.//@details)')) as details
  	, CONVERT(NUMERIC(18,3),CONVERT(VARCHAR(1000),TLOG.c.query('data(.//@value)'))) as value
  	, CONVERT(VARCHAR(100),TLOG.c.query('data(.//@target)')) as target
  	, getdate()
  	from @xml.nodes('/Logs/LogDateEntry/Details') as TLOG(c)
  	GO

  view raw XML_xquery_sample.sql hosted with ❤ by GitHub