Sql server 将XML数据插入到表中

Sql server 将XML数据插入到表中

sql-serverxml

Sql server 将XML数据插入到表中,sql-server,xml,Sql Servermssql 在表中插入,Xml,我需要将外部XML文件数据插入SQL Server表中。我尝试了下面的代码。但这会为所有列插入一条带有NULL值的记录Declare @xml XMLSelect @xml =CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'C:\Users\PC901\Downloads\Tags.xml',SINGLE_BLOB) AS XSET ARITHABORT ONTRUNCATE TABLE TagsInsert into Tags

我需要将外部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"?>

   
  
  
  
  

给你：

declare @xml xml = '<?xml version="1.0" encoding="utf-8"?>

  

  
  
  
  
'
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)

（编辑：成都站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!