XML fields compare

[v***@gmail.com]

I have got 3 fields.
1.field [OldContent] [xml] NULL,
2.field [NewContent] [xml] NULL,
3.[OldContent] [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="İZMİR" />

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


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



Loading Image...


Loading Image...

[Erland Sommarskog]

I had to solve a similar problem a while back, and the below is drawn from
that solution. Credits goes to my friend wBob, who came up with the idea to
use .modify.

I had to change the city name, since my newsreader is shamefully enough only
8-bit.

What if an attribute is only present in old content? What should happen in
that case?

DECLARE @old xml = '<row GUEST_ID="13" GUEST_NAME="VEDAT"
GUEST_SURNAME="PALA" ADRESS="SMYRNA" />',
@new xml = '<row GUEST_ID="13" GUEST_NAME="VEDAT"
GUEST_SURNAME="PALA" ADRESS="SMYRNA" CITY="DR" CITY_CODE="35" />'

DECLARE @attr sysname

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT attr
FROM (SELECT T.c.value('local-name(.)', 'nvarchar(128)') AS attr,
T.c.value('.', 'nvarchar(4000)') AS oldvalue,
NULL AS newvalue
FROM @old.nodes('/row/@*') AS T(c)
UNION ALL
SELECT T.c.value('local-name(.)', 'nvarchar(128)') AS attr,
NULL,
T.c.value('.', 'nvarchar(4000)') AS newvalue
FROM @new.nodes('/row/@*') AS T(c)) AS u
GROUP BY attr
HAVING MIN(oldvalue) = MIN(oldvalue)

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @attr
IF @@fetch_status <> 0
BREAK

SET @new.modify('delete /row/@*[local-name(.)=sql:variable("@attr")]')
END

DEALLOCATE cur

SELECT @new

[Vedat Pala]

Post by Erland Sommarskog
I had to solve a similar problem a while back, and the below is drawn from
that solution. Credits goes to my friend wBob, who came up with the idea to
use .modify.
I had to change the city name, since my newsreader is shamefully enough only
8-bit.
What if an attribute is only present in old content? What should happen in
that case?
GUEST_SURNAME="PALA" ADRESS="SMYRNA" />',
@new xml = '<row GUEST_ID="13" GUEST_NAME="VEDAT"
GUEST_SURNAME="PALA" ADRESS="SMYRNA" CITY="DR" CITY_CODE="35" />'
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT attr
FROM (SELECT T.c.value('local-name(.)', 'nvarchar(128)') AS attr,
T.c.value('.', 'nvarchar(4000)') AS oldvalue,
NULL AS newvalue
UNION ALL
SELECT T.c.value('local-name(.)', 'nvarchar(128)') AS attr,
NULL,
T.c.value('.', 'nvarchar(4000)') AS newvalue
GROUP BY attr
HAVING MIN(oldvalue) = MIN(oldvalue)
OPEN cur
WHILE 1 = 1
BEGIN
BREAK
END
DEALLOCATE cur

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'

[Erland Sommarskog]

Post by Vedat Pala
The solution is not complete enough because;
@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'

So had you provided more complete test data, I would have caught that bug.
There's a trivial typo in this line:

HAVING MIN(oldvalue) = MIN(oldvalue)

There is still a case like this one:

DECLARE @old xml = '<row GUEST_ID="13" GUEST_NAME="VEDAT35"
GUEST_SURNAME="PALA" ADRESS="SMYRNA" EXTRA="2"/>',
@new xml = '<row GUEST_ID="13" GUEST_NAME="VEDAT"
GUEST_SURNAME="PALA" ADRESS="SMYRNA" CITY="DR" CITY_CODE="35" />'

That is, there is a column which is non-NULL in the old row, but has NULL
in the new row. My script does not handle this, because I don't know
how you want to deal with this. (In my case where I did this, I have
two columns afterimage and beforeimage and I delete attributes that
are the same from both values, save for key values, why this sorts
out anyway.)