我希望能够在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