将XmlFile加载到Sql-server中
本文关键字：Sql-server 加载 XmlFile | 更新日期: 2023-09-27 18:08:29
我正在寻找加载我的XmlFile到Sql-Server，我将使用sqlbulkcopy，但我不知道如何将我的XmlFile转换为数据集，因为它们有很多imbriqued表。这是我的XmlFile，提前谢谢你。
<?xml version="1.0" encoding="utf-8"?>
<data>
<data>
<created_time>2016-09-23T22:37:33+01:00</created_time>
<id>12451</id>
<message>Cool</message>
<from>
<id>1</id>
<name>user1</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic.jpg</url>
</data>
</picture>
</from>
<comments>
<data>
<created_time>2016-09-23T22:46:59+01:00</created_time>
<id>112</id>
<message>ok</message>
<from>
<id>1245</id>
<name>reply user1</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic2.jpg</url>
</data>
</picture>
</from>
</data>
<data>
<created_time>2016-09-23T22:47:41+01:00</created_time>
<id>113</id>
<message>nice</message>
<from>
<id>1246</id>
<name>reply user2</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic3.jpg</url>
</data>
</picture>
</from>
</data>
<paging>
<cursors>
<before>sdfdfdsfdfdsfdsf</before>
<after>dsfdsfdfsfdffdfdf</after>
</cursors>
</paging>
</comments>
</data>
<data>
<created_time>2016-09-23T22:35:20+01:00</created_time>
<id>47854</id>
<message>Thank you</message>
<from>
<id>365</id>
<name>user2</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic4.jpg</url>
</data>
</picture>
</from>
<comments>
<data>
<created_time>2016-09-23T22:47:05+01:00</created_time>
<id>1285</id>
<message>what?</message>
<from>
<id>33</id>
<name>reply user1</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic5.jpg</url>
</data>
</picture>
</from>
</data>
<data>
<created_time>2016-09-23T22:55:54+01:00</created_time>
<id>1982</id>
<message>No.</message>
<from>
<id>102</id>
<name>reply user2</name>
<picture>
<data>
<is_silhouette>false</is_silhouette>
<url>https://server.com/pic6.jpg</url>
</data>
</picture>
</from>
</data>
</comments>
</data>
</data>
您可以使用SSIS或在sql server中读取xml来获取更多信息:将XML文件中的数据导入并处理到SQL Server表
sql-server-simple-example-of-reading-xml-file-using-t-sql
您可以创建一个类型为varbinary(max)的数据库列，它将映射到域模型中的byte[]属性。然后使用XmlWriter将XML文档存储到该列中。
public virtual void Save(
XmlWriter w
)
使用下面的查询，您将在派生表中获得XML中的所有数据。
应该很容易将它插入到适当的表中。提示:
SELECT DISTINCT ...
SELECT col1, col2 INTO #tbl ...
INSERT INTO tbl SELECT col1, col2, ...
对于id的创建，您可能会使用ROW_NUMBER() OVER()与合适的PARTITION BY…
XML>
SELECT data.value('created_time[1]','datetime') AS Data_CreatedTime
,data.value('id[1]','int') AS Data_Id
,data.value('message[1]','nvarchar(max)') AS Data_Message
,data.value('(from/id)[1]','int') AS Data_From_Id
--more values here
,data.value('(from/picture/data/url)[1]','nvarchar(max)') AS Data_From_Picture_URL
--more values here
,comment.value('created_time[1]','datetime') AS Comment_CreatedTime
,comment.value('id[1]','int') AS Comment_Id
,comment.value('message[1]','nvarchar(max)') AS Comment_Message
,comment.value('(from/id)[1]','int') AS Comment_From_Id
--more values here
,comment.value('(from/picture/data/url)[1]','nvarchar(max)') AS Comment_From_Picture_URL
--more values here
FROM @xml.nodes('/data/data') AS A(data)
OUTER APPLY data.nodes('comments/data') AS B(comment)
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
| Data_CreatedTime | Data_Id | Data_Message | Data_From_Id | Data_From_Picture_URL | Comment_CreatedTime | Comment_Id | Comment_Message | Comment_From_Id | Comment_From_Picture_URL |
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
| 2016-09-23 21:37:33.000 | 12451 | Cool | 1 | https://server.com/pic.jpg | 2016-09-23 21:46:59.000 | 112 | ok | 1245 | https://server.com/pic2.jpg |
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
| 2016-09-23 21:37:33.000 | 12451 | Cool | 1 | https://server.com/pic.jpg | 2016-09-23 21:47:41.000 | 113 | nice | 1246 | https://server.com/pic3.jpg |
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
| 2016-09-23 21:35:20.000 | 47854 | Thank you | 365 | https://server.com/pic4.jpg | 2016-09-23 21:47:05.000 | 1285 | what? | 33 | https://server.com/pic5.jpg |
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
| 2016-09-23 21:35:20.000 | 47854 | Thank you | 365 | https://server.com/pic4.jpg | 2016-09-23 21:55:54.000 | 1982 | No. | 102 | https://server.com/pic6.jpg |
+-------------------------+---------+--------------+--------------+-----------------------------+-------------------------+------------+-----------------+-----------------+-----------------------------+
最后OK，解决方案是将XmlFile加载到MS-SQL服务器。在我的c#程序中，我使用以下命令:
cmd.CommandText = "Insert into Table_fileXml(xml) SELECT CONVERT(XML, BulkColumn) AS BulkColumn From OPENROWSET(BULK 'd:''XmlFile.xml', SINGLE_BLOB) AS X";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
最后创建一个触发器，将执行存储过程之后，我的文件XmlFile将被插入到我的MS-SQL Server数据库:
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigger1]
ON [dbo].[Table_FileXml]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @xml xml
SELECT top(1) @xml=xml FROM Table_fileXml ORDER BY id DESC
SELECT data.value('created_time[1]','datetime') AS Data_CreatedTime
,data.value('id[1]','nvarchar(max)') AS Data_Id
,data.value('message[1]','nvarchar(max)') AS Data_Message
,data.value('(from/id)[1]','nvarchar(50)') AS Data_From_Id
--more values here
,data.value('(from/picture/data/url)[1]','nvarchar(max)') AS Data_From_Picture_URL
--more values here
,comment.value('created_time[1]','datetime') AS Comment_CreatedTime
,comment.value('id[1]','nvarchar(max)') AS Comment_Id
,comment.value('message[1]','nvarchar(max)') AS Comment_Message
,comment.value('(from/id)[1]','nvarchar(50)') AS Comment_From_Id
--more values here
,comment.value('(from/picture/data/url)[1]','nvarchar(max)') AS Comment_From_Picture_URL
--more values here
FROM @xml.nodes('/data/data') AS A(data)
OUTER APPLY data.nodes('comments/data') AS B(comment)
BEGIN
DECLARE @xml2 xml
SELECT top(1) @xml2=xml FROM Table_fileXml ORDER BY id DESC
INSERT INTO Table_comment(from_id,from_pic,comment_message)
SELECT DISTINCT comment.value('(from/id)[1]','nvarchar(50)') AS from_id,
data.value('(from/picture/data/url)[1]','nvarchar(max)') AS from_pic,
comment.value('message[1]','nvarchar(max)') AS comment_message
FROM @xml2.nodes('/data/data') AS A(data)
OUTER APPLY data.nodes('comments/data') AS B(comment)
END
END
结果是正确的。使用不带存储过程的触发器只给我FileXML中的最后一行。这就是为什么我必须使用带有触发器的存储过程。
感谢"Shnugo"、"Martin Staufcik"answers"Abouzar"的宝贵帮助。