Discussion:
Two XML data compare(for logging)
(too old to reply)
Vedat Pala
2015-07-07 21:30:33 UTC
Permalink
I just want to see changing values.

I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.fiedl [UpdateContent] [xml] NULL,

I want to compare the data in two fields.
Results I want to write to the 3.field.([OldContent])

How can I do it in SQL server?

[OldContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" />

[NewContent] [xml] NULL, in value
<row GUEST_ID="13" GUEST_NAME="VEDAT" GUEST_SURNAME="PALA" ADRESS="IZMIR" CITY="DR" CITY_CODE="35" />


[UpdateContent] [xml] NULL, I want to write the value in it.
<row CITY="DR" CITY_CODE="35" /

1)now table status
Loading Image...

2)i want this
Loading Image...

I keep a log transformation of the table.
This triger


USE [DENEME]
GO
/****** Object: Trigger [dbo].[iudt_AutoAuditChanges] Script Date: 08.07.2015 00:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[iudt_AutoAuditChanges]
ON [dbo].[GUESTS]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @AuditType char(1) /* BEN EKELEDM*/
Declare @v_AuditID bigint

/******************************/
if exists (select * from inserted)
if exists (select * from deleted)
SET @AuditType = 'U'
else
SET @AuditType = 'I'
else
SET @AuditType = 'D'
/******************************/

IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
CREATE TABLE [dbo].[AutoAudit]
( [AuditID] bigint identity,
[AuditType] Char(1),
[AuditDate] DateTime,
[AuditUserName] varchar(128),
[TableName] varchar(128) NULL,
[OldContent] XML NULL,
[NewContent] XML NULL
)

ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
PK_AutoAudit PRIMARY KEY CLUSTERED
(
[AuditID]
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit]
( [TableName] ASC,
[AuditDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

Select * Into #AuditDeleted from deleted
Select * Into #AuditInserted from inserted

While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
Begin

INSERT INTO [dbo].[AutoAudit]
( [AuditDate],[AuditType], [AuditUserName], [TableName], [OldContent], [NewContent])
SELECT
GETDATE(),
@AuditType,
SUSER_NAME(),
[TableName]=object_name([parent_obj]),
[OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
[NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
FROM sysobjects
WHERE
[xtype] = 'tr'
and [name] = OBJECT_NAME(@@PROCID)

Set @v_AuditID = SCOPE_IDENTITY()

Delete from AutoAudit
Where AuditID = @v_AuditID
AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)

Delete top(1) from #AuditDeleted
Delete top(1) from #AuditInserted

End
END
Erland Sommarskog
2015-07-07 22:10:44 UTC
Permalink
Post by Vedat Pala
I just want to see changing values.
I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.fiedl [UpdateContent] [xml] NULL,
I want to compare the data in two fields.
Results I want to write to the 3.field.([OldContent])
How can I do it in SQL server?
Please check your previous thread to which I posted a solution.
Vedat Pala
2015-07-07 23:57:15 UTC
Permalink
Post by Erland Sommarskog
Post by Vedat Pala
I just want to see changing values.
I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.fiedl [UpdateContent] [xml] NULL,
I want to compare the data in two fields.
Results I want to write to the 3.field.([OldContent])
How can I do it in SQL server?
Please check your previous thread to which I posted a solution.
The solution is not complete enough because;
DECLARE @old xml = '<row GUEST_ID="14" GUEST_NAME="VEDAT" />',
@new xml = '<row GUEST_ID="14" GUEST_NAME="VEDAT35" GUEST_SURNAME="PALA" />" '

your result only
<row GUEST_SURNAME="PALA"
but true result
GUEST_NAME="VEDAT35" GUEST_SURNAME="PALA"
why
old value vedat---> new value(updated) VEDAT35
old value SURNAME='' new value(updated) 'PALA'

Loading...