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

- Susanna Floora

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 

- Pரதீப்

Answer 2

使用以下查询。

with cte_1
AS
(SELECT UL.U.value('@CustID','nvarchar(100)') as CustID,
        UL.U.value('@CustName','nvarchar(100)') as Customer,
        UL.U.value('@Address','nvarchar(100)') as [Address],
        NV.U.value('@CustName','nvarchar(100)') as NewCustomer,
        NV.U.value('@Address','nvarchar(100)') as [NewAddress]
 FROM   UpdationLog
 CROSS APPLY OldValue.nodes('/ccm') as UL(U)
 CROSS APPLY NewValue.nodes('/ccm') as NV(U))

SELECT CustID,'CustName' as Attribute,Customer OldValue,NewCustomer NewValue
FROM cte_1
UNION ALL
SELECT CustID,'Address' as Attribute,[Address] OldValue,[NewAddress] NewValue
FROM cte_1
ORDER BY  CustID

- Unnikrishnan R

Answer 3

使用local-name function的另一种方法来获取属性名称：

SELECT  CustID,
        CAST(t.c.query('local-name(.)') as nvarchar(max)) Attribute,
        t.c.value('.','nvarchar(max)') OldValue,
        u.d.value('.','nvarchar(max)') NewValue
FROM UpdationLog
CROSS APPLY OldValue.nodes('//@*') as t(c)
CROSS APPLY NewValue.nodes('//@*') as u(d)
WHERE 
    CAST(t.c.query('local-name(.)') as nvarchar(max))  = CAST(u.d.query('local-name(.)') as nvarchar(max))
    AND CAST(t.c.query('local-name(.)') as nvarchar(max)) != 'CustID'
    AND t.c.value('.','nvarchar(max)') != u.d.value('.','nvarchar(max)')

输出：

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

- gofr1