Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31259

Re: XML fields compare

Show all headers | View raw

7 Temmuz 2015 Salı 22:39:32 UTC+3 tarihinde Erland Sommarskog yazdı:
> 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

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'

Back to microsoft.public.sqlserver.programming | Previous | Next | Find similar

Thread

Re: XML fields compare Vedat Pala <vedatpala@gmail.com> - 2015-07-07 16:55 -0700

csiph-web