Блог

Я хочу сравнить два столбца XML с несколькими строками в SQL Server 2005.

Структура таблицы выглядит следующим образом

 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

Вот один из способов. Не уверен, что это идеальный метод, но он должен получить то, что вы ищете

 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 
  

Используйте приведенный ниже запрос.

 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
  

Еще один способ с использованием local-name функции для получения имени атрибута:

 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