c# - 从 XML 更新插入 SQL Server 表

Question

我正尝试在 C# 中创建一个小型控制台应用程序，以尽可能以最快的方式根据 XML 文件的内容对 SQL Server 2008 中的产品表 (ITEMS) 执行插入操作。我已经有一个 .XSD 文件，其中包含到 SQL 表的正确映射(使用下面概述的方法可能不需要)。

这是我的方法的概要:

1. 读取 XML，使用它创建一个表。
2. 使用从 XML 文件创建的表对 ITEMS 表执行 MERGE。
   2a.如果该项目存在，请更新它。
   2b.如果该项目不存在，请将其插入。
3. 创建仅包含插入 XML 中的记录的日志。

考虑以下 ITEMS 表和 XML 文件:

元素

  Item_Id    Name    Price  
     1       Coke     5.00  
     2       Pepsi    3.00  
     3       Sprite   2.00   

ITEMS.XML

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>
   <Item>
    <Id>3</Id>
    <Name>Sprite Zero</Name>
    <Price>1.75</Price>
   </Item>

导入后，ITEMS 表应如下所示:

元素

  Item_Id    Name         Price  
     1       Coke          5.00  
     2       Pepsi         3.00  
     3       Sprite Zero   1.75  
     5       Mountain Dew  4.50

一旦完成，我还需要生成一个 XML 格式的日志文件，其中包含插入到表中的"new"记录 (ITEMS_LOG.XML):

ITEMS_LOG.XML

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>

我已经尝试使用 SQLXMLBulkLoad 来实现它，但不幸的是它没有提供我需要的日志记录，也不允许我访问从 SQL Server 返回的任何消息(即插入/更新的消息)。尽管我具有中等水平的 SQL 专业知识，但我对使用 XML 还很陌生，尤其是在这种情况下。任何帮助/指导将不胜感激!

Answer 1

您可以使用 merge 和 output 到表变量，然后查询表变量以构建日志 XML。

将它放在一个存储过程中，您将项目 XML 作为输入参数，并将日志 XML 作为输出参数。

create procedure AddItemXML
  @ItemsXML xml,
  @ItemsLogXML xml out
as

declare @Changes table
(
  Item_Id int,
  Name nvarchar(20),
  Price money,
  Action nvarchar(10)
);

merge Items as T
using
  (
    select T.N.value('Id[1]', 'int') as Item_Id,
           T.N.value('Name[1]', 'varchar(20)') as Name,
           T.N.value('Price[1]', 'money') as Price
    from @ItemsXML.nodes('/Item') T(N)
  ) as S
on T.Item_Id = S.Item_Id
when matched then
  update set Name = S.Name, Price = S.Price
when not matched then
  insert (Item_Id, Name, Price) values (S.Item_Id, S.Name, S.Price)
output inserted.Item_Id,
       inserted.Name,
       inserted.Price,
       $action 
  into @Changes;

set @ItemsLogXML = 
  (
    select Item_Id as ID,
           Name,
           Price
    from @Changes
    where Action = 'INSERT'
    for xml path('Item'), type
  );

SE-Data 上的工作示例