时间戳

首页 > 解决方案 > 将 XML 数据插入到表中

sql-server - 将 XML 数据插入到表中

问题描述

我需要将外部 XML 文件数据插入到 SQL Server 表中。我尝试了下面的代码。但这会插入一条记录，其中NULL包含所有列的值

Declare @xml XML

Select @xml =
CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 
'C:\Users\PC901\Downloads\Tags.xml',SINGLE_BLOB) AS X

SET ARITHABORT ON
TRUNCATE TABLE Tags

Insert into Tags
(
ID,WikiPostId,ExcerptPostId,Count,TagName
)

Select
P.value('ID[1]','BIGINT') AS ID,
P.value('WikiPostId[1]','BIGINT') AS WikiPostId,
P.value('ExcerptPostId[1]','BIGINT') AS ExcerptPostId,
P.value('Count[1]','BIGINT') AS Count,
P.value('TagName[1]','VARCHAR(100)') AS TagName
From @xml.nodes('/tags') PropertyFeed(P)

SELECT * FROM Tags

并且示例 XML 将是

<?xml version="1.0" encoding="utf-8"?>
<tags>
   <row Id="1" TagName=".net" Count="283778" ExcerptPostId="3624959" WikiPostId="3607476" />
  <row Id="2" TagName="html" Count="826083" ExcerptPostId="3673183" WikiPostId="3673182" />
  <row Id="3" TagName="javascript" Count="1817846" ExcerptPostId="3624960" WikiPostId="3607052" />
  <row Id="4" TagName="css" Count="588062" ExcerptPostId="3644670" WikiPostId="3644669" />
  <row Id="5" TagName="php" Count="1286873" ExcerptPostId="3624936" WikiPostId="3607050" />
</tags>

标签： sql-serverxml

解决方案

干得好：

declare @xml xml = '<?xml version="1.0" encoding="utf-8"?>
<tags>
  <row Id="1" TagName=".net" Count="283778" ExcerptPostId="3624959" WikiPostId="3607476" />
  <row Id="2" TagName="html" Count="826083" ExcerptPostId="3673183" WikiPostId="3673182" />
  <row Id="3" TagName="javascript" Count="1817846" ExcerptPostId="3624960" WikiPostId="3607052" />
  <row Id="4" TagName="css" Count="588062" ExcerptPostId="3644670" WikiPostId="3644669" />
  <row Id="5" TagName="php" Count="1286873" ExcerptPostId="3624936" WikiPostId="3607050" />
</tags>'

Select
P.value('@Id','BIGINT') AS ID,
P.value('@WikiPostId','BIGINT') AS WikiPostId,
P.value('@ExcerptPostId','BIGINT') AS ExcerptPostId,
P.value('@Count','BIGINT') AS Count,
P.value('@TagName','VARCHAR(100)') AS TagName
From @xml.nodes('/tags/row') PropertyFeed(P)

输出

ID          WikiPostId           ExcerptPostId        Count                TagName
----------- -------------------- -------------------- -------------------- ----------
1           3607476              3624959              283778               .net
2           3673182              3673183              826083               html
3           3607052              3624960              1817846              javascript
4           3644669              3644670              588062               css
5           3607050              3624936              1286873              php

(5 rows affected)

推荐阅读