sql - SQl server 2005 中的 XML 列比较

Question

我想在 SQL Server 2005 中比较具有多行的两个 XML 列。

表结构如下

CREATE TABLE [dbo].[UpdationLog](
    [LogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CustID] [int] NOT NULL,
    [OldValue] [xml] NOT NULL,
    [NewValue] [xml] NOT NULL,
 CONSTRAINT [PK_UpdationLog] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[UpdationLog] VALUES
(1526,'<ccm CustID="1526" CustName="Teja" Address="Bangalore"/>','<ccm CustID="1526" CustName="Tejas" Address="Bengaluru"/>'),
(1245,'<ccm CustID="1245" CustName="Abhi" Address="Andhra"/>','<ccm CustID="1245" CustName="Abhilash" Address="Andra Pradesh"/>'),
(1145,'<ccm CustID="1145" CustName="Abhi" Address="Assam"/>','<ccm CustID="1145" CustName="Abhinandan" Address="Assam"/>')

我想比较 XML 列 OldValue 和 NewValue 并显示更新的记录。

期望的输出

|-------|-------------|---------------|------------
|CustID |   Attribute |     OldValue  | NewValue
|-------|-------------|---------------|---------
|1526   | CustName    |  Teja         | Tejas
|1526   | Address     | Bangalore     | Bengaluru
|1245   | CustName    |  Abhi         | Abhilash
|1245   | Address     |  Andhra       | Andra Pradesh
|1145   | CustName    |  Abhi         | Abhinandan

http://sqlfiddle.com/#!3/cb0b3/1

Answer 1

这是一种方法。不确定这是理想的方法，但应该得到你正在寻找的东西

SELECT CustID,
       Attribute,
       Max(CASE WHEN iden = 'old' THEN val END) AS OldValue,
       Max(CASE WHEN iden = 'new' THEN val END) AS NewValue
FROM   (SELECT o.value('@CustID', 'int')           AS CustID,
               o.value('@CustName', 'varchar(50)') AS CustName,
               o.value('@Address', 'varchar(500)') AS Address,
               'old'                               AS iden
        FROM   UpdationLog
               CROSS apply [OldValue].nodes('ccm') a(o)
        UNION ALL
        SELECT n.value('@CustID', 'int')           AS CustID,
               n.value('@CustName', 'varchar(50)') AS CustName,
               n.value('@Address', 'varchar(500)') AS Address,
               'new'                               AS iden
        FROM   UpdationLog
               CROSS apply [NewValue].nodes('ccm') b(n)) a
       CROSS apply (SELECT CustName, 'CustName'
                    UNION ALL
                    SELECT Address, 'Address') tc (val, Attribute)
GROUP  BY CustID,
          Attribute