sql-server - 使用 XML 字符串更新表中的数据

Question

我有一个表，其中包含像

这样的列

• 站点 ID (identity_Col)
• 站点名称
• POrderID
• 位置
• 地址
• 集群
• 供应商名称

我想使用 xml 字符串在此表中插入/更新/删除数据。除此列外，XML 字符串还包含一列，即。行信息。此列将具有“未更改”、“更新”、“新建”、“删除”等值。基于这个值，表中的行应该被插入、更新、删除。

我的 XML 字符串如下:

<NewDataSet>
  <DataTable>
     <SiteID>2</SiteID>
     <SiteName>NIZAMPURA</SiteName>
     <POrderID>7</POrderID>
     <Location>NIZAMPURA</Location>
     <SiteAddress>Vadodara</SiteAddress>
     <Cluster>002</Cluster>
     <SubVendorName>Test Vender-1</SubVendorName>
     <RowInfo>UNCHANGED</RowInfo>
  </DataTable>
  <DataTable>
     <SiteID>16</SiteID>
     <SiteName>Site-1</SiteName>
     <POrderID>7</POrderID>
     <Location>Alkapuri</Location>
     <SiteAddress>test</SiteAddress>
     <Cluster>Test Cluster</Cluster>
     <SubVendorName>Test Vender12</SubVendorName>
     <RowInfo>UNCHANGED</RowInfo>
  </DataTable>
  <DataTable>
     <SiteID>17</SiteID>
     <SiteName>Site-3</SiteName>
     <POrderID>7</POrderID>
     <Location>Alkapuri123</Location>
     <SiteAddress>test123</SiteAddress>
     <Cluster>Test Cluster123</Cluster>
     <SubVendorName>Test Vender123</SubVendorName>
     <RowInfo>DELETE</RowInfo>
  </DataTable>
</NewDataSet>'

这是我编写的用于在 RowInfo = "NEW"时向表中插入数据的代码

IF len(ISNULL(@xmlString, '')) > 0
    BEGIN
    DECLARE @docHandle1 int = 0;
    EXEC sp_xml_preparedocument @docHandle1 OUTPUT, @xmlString

    INSERT INTO [SiteTRS] (
                [SiteName],
                [POrderID],
                [Location],
                [SiteAddress],
                [Cluster],
                [SubVendorName])
    SELECT SiteName,POrderID,Location,SiteAddress,Cluster,SubVendorName
        FROM OPENXML (@docHandle1, '/NewDataSet/DataTable')

        WITH (SiteName varchar(50) './SiteName',
          POrderID varchar(50) './PorderID',
          Location varchar(50) './Location',
          SiteAddress varchar(max) './SiteAddress',
          Cluster varchar(50) './Cluster',
          SubVendorName varchar(50) './SubVendorName',
          RowInfo varchar(30) './RowInfo')   
    WHERE RowInfo='NEW' 

但是我不知道如何使用XML来更新/删除表中的记录。请指导我是 XML 的新手，所以不知道。如果我做了一些幼稚的事情，请原谅我。

Answer 1

我强烈建议不要再使用旧的、遗留的 OPENXML 东西 - 有了 SQL Server 中的 XML 支持，使用内置的 XPath/XQuery 方法就容易多了。

在您的情况下，我会使用 CTE(通用表表达式)将 XML 分解为行和列的“内联”表:

DECLARE @input XML = '<NewDataSet>
  <DataTable>
    <SiteID>2</SiteID>
    <SiteName>NIZAMPURA</SiteName>
    <POrderID>7</POrderID>
    <Location>NIZAMPURA</Location>
    <SiteAddress>Vadodara</SiteAddress>
    <Cluster>002</Cluster>
    <SubVendorName>Vender-1</SubVendorName>
    <RowInfo>UPDATE</RowInfo>
  </DataTable>
  <DataTable>
    <SiteName>Site-1</SiteName>
    <POrderID>7</POrderID>
    <Location>Alkapuri</Location>
    <SiteAddress>test</SiteAddress>
    <Cluster>Cluster-1</Cluster>
    <SubVendorName>Test Vender</SubVendorName>
    <RowInfo>NEW</RowInfo>
  </DataTable>
</NewDataSet>'

;WITH XMLData AS
(
    SELECT
        NDS.DT.value('(SiteID)[1]', 'int') AS 'SiteID',
        NDS.DT.value('(SiteName)[1]', 'varchar(50)') AS 'SiteName',
        NDS.DT.value('(POrderID)[1]', 'int') AS 'POrderID',
        NDS.DT.value('(Location)[1]', 'varchar(100)') AS 'Location',
        NDS.DT.value('(SiteAddress)[1]', 'varchar(100)') AS 'SiteAddress',
        NDS.DT.value('(Cluster)[1]', 'varchar(100)') AS 'Cluster',
        NDS.DT.value('(SubVendorName)[1]', 'varchar(100)') AS 'SubVendorName',
        NDS.DT.value('(RowInfo)[1]', 'varchar(20)') AS 'RowInfo'
    FROM 
        @input.nodes('/NewDataSet/DataTable') AS NDS(DT)
)
SELECT *
FROM XMLDATA

这为您提供了可以使用的行和列。

SiteID  SiteName   POrderID Location   SiteAddress  Cluster    SubVendorName   RowInfo
  2     NIZAMPURA     7     NIZAMPURA  Vadodara     002        Vender-1        UPDATE
 NULL   Site-1        7     Alkapuri   test         Cluster-1  Test Vender     NEW

现在，如果您使用的是 SQL Server 2008 或更新版本，您可以将它与 MERGE command 结合使用基本上在一条语句中完成您的INSERT/UPDATE。

如果您使用的是 2005 年，则需要将此信息存储到存储过程中的临时表/表变量中，或者需要多次进行选择； CTE 只允许一个命令跟随它。

更新:使用此 CTE，您可以将其与 MERGE 组合:

;WITH XmlData AS 
(
    SELECT
        NDS.DT.value('(SiteID)[1]', 'int') AS 'SiteID',
        NDS.DT.value('(SiteName)[1]', 'varchar(50)') AS 'SiteName',
        NDS.DT.value('(POrderID)[1]', 'int') AS 'POrderID',
        NDS.DT.value('(Location)[1]', 'varchar(100)') AS 'Location',
        NDS.DT.value('(SiteAddress)[1]', 'varchar(100)') AS 'SiteAddress',
        NDS.DT.value('(Cluster)[1]', 'varchar(100)') AS 'Cluster',
        NDS.DT.value('(SubVendorName)[1]', 'varchar(100)') AS 'SubVendorName',
        NDS.DT.value('(RowInfo)[1]', 'varchar(20)') AS 'RowInfo'
    FROM 
        @input.nodes('/NewDataSet/DataTable') AS NDS(DT)
)
MERGE INTO dbo.SiteTRS t
USING XmlData x ON t.SiteID = x.SiteID
WHEN MATCHED AND x.RowInfo = 'UPDATE'
   THEN 
     UPDATE SET 
        t.SiteName = x.SiteName,
        t.POrderID = x.POrderID,
        t.Location = x.Location,
        t.SiteAddress = x.SiteAddress,
        t.Cluster = x.Cluster,
        t.SubVendorName = x.SubVendorName

WHEN MATCHED AND x.RowInfo = 'DELETE'
   THEN DELETE 

WHEN NOT MATCHED AND x.RowInfo = 'NEW'
   THEN 
      INSERT(SiteID, SiteName, POrderID, Location, SiteAddress, Cluster, SubVendorName)
      VALUES(x.SiteID, x.SiteName, x.POrderID, x.Location, x.SiteAddress, x.Cluster, x.SubVendorName)
;

查看更多资源:

• SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
• Using SQL Server 2008's MERGE statement