从 XML 更新插入到 SQL Server 表

本文关键字：SQL Server 插入 XML 更新 | 更新日期: 2023-09-27 18:32:38

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

以下是我方法的一个高级：

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

请考虑以下 ITEMS 表和 XML 文件：

项目 

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

项目

.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 格式的日志文件，其中包含插入到表 (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相当陌生，特别是在这种情况下。 任何帮助/指导将不胜感激！

从 XML 更新插入到 SQL Server 表

您可以将merge与表变量output一起使用，然后查询表变量以生成日志 XML。

将其放在存储过程中，其中项 XML 作为 in 参数，将日志 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 数据的工作示例

希望对您有所帮助，我所做的是创建一个存储过程，如下所示。基本上，存储过程采用 xml 值并检查从代码传递的标志，并确定它是插入还是更新：

DECLARE @xml xml
SET @xml = @xmlCredentials
SELECT
      item.value('@Id', 'int') As ID,
      item.value('@AgentID', 'int') As AgentID,
      item.value('@Username', 'varchar (50)') As Username,
      item.value('@Password', 'varchar (50)') As [Password],
      item.value('@IsDirty', 'bit') As IsDirty,
      item.value('@IsDeleted', 'bit') As IsDeleted
INTO #tmp
FROM @xml.nodes('Credentials/Credential') x(item)
BEGIN TRY
BEGIN TRAN
      INSERT INTO Credentials (AgentID, Username, [Password])
          SELECT
              AgentID, Username, [Password]
          FROM
              #tmp
          WHERE
              ID = 0 AND IsDirty = 1
      UPDATE c
      SET c.[AgentID] = t.AgentID,
          c.[Username] = t.Username,
          c.[Password] = t.[Password]
      FROM
          [dbo].[Credentials] c
      JOIN 
          #tmp t ON t.Id = c.ID
      WHERE
          t.IsDirty = 1 AND t.IsDeleted = 0
      DELETE FROM [dbo].[Credentials]
      FROM [dbo].[Credentials] c
      JOIN #tmp t ON t.Id = c.ID
      WHERE 
          t.IsDirty = 1 AND t.IsDeleted = 1
      COMMIT TRAN
END TRY
BEGIN CATCH
      IF @@TRANCOUNT > 0
            ROLLBACK TRAN
      DECLARE @errorMSG varchar(4000)
      DECLARE @errorSeverity int
      DECLARE @errorState int
      SET @errorMSG = ERROR_MESSAGE()
      SET @errorSeverity = ERROR_SEVERITY()
      SET @errorState = ERROR_STATE()
      RAISERROR (@errorMSG,
                        @errorSeverity, @errorState);
END CATCH
SELECT [ID], [AgentID], [Username], [Password]
FROM [dbo].[Credentials]

在代码隐藏中，我有我的 xml 并将 xml 作为参数传递给存储过程：

// read xml and assign it to string variable
string xml = readxml();
try
{
    string command = "EXEC SaveCredentails '" + xml + "'";
}
catch(Exception e)
{
}

我会使用临时表将 xml 导入到 SQL Server 表中。添加额外的列以指示操作(插入或更新(。然后像往常一样使用常规 sql 执行更新插入。然后，您可以使用临时表生成所需的 XML 日志记录(读取操作列以确定它是插入还是更新(。

相关文章：