x

update xml column data

HI THERE, i have been tasked with updating an audir table that contains an xml row. i want to anonymise data where based on like. for example the table structure is as follows:

 CREATE TABLE [dbo].[TD_AUDIT_V1](
     [AUD_ID] [int] IDENTITY(1,1) NOT NULL,
     [AUD_TABLE_NAME] [nvarchar](50) NOT NULL,
     [AUD_ROW_ID] [int] NOT NULL,
     [AUD_ROW_VERSION] [int] NOT NULL,
     [AUD_CHANGE_TYPE] [char](1) NOT NULL,
     [AUD_ROW_DATA] [xml] NOT NULL,
     [AUD_USER_CHANGED] [nvarchar](50) NOT NULL,
     [AUD_DATE_CHANGED] [datetime] NOT NULL,
     [AUD_TO_ARCHIVE] [bit] NOT NULL,
     [AUD_TO_DELETE] [bit] NOT NULL,
     [AUD_TRAN_ID] [uniqueidentifier] NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The aud_row_data is updated from a trigger when a new row is inserted or updated from another table. i tried to update the data with the following code:

 update [dbo].[TD_AUDIT_V1] set
     aud_row_data.modify('
         replace value of 
         (
             /aud_row_data[APP_IBAN[contains(., "ABCD")]
             ]/APP_IBAN/text()
         )[1]
         with "AB00CDEF00000000000000"');

when i ran the code it updated a number of rows but when i ran select based on the row being like AB00CDEF00000000000000 i got now rows back.

has anyone had any previous experience in a task like this?

Thanks in advance.

more ▼

asked Apr 13, 2016 at 02:54 PM in Default

avatar image

niall5098
21 1 2 4

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x438
x202
x174

asked: Apr 13, 2016 at 02:54 PM

Seen: 38 times

Last Updated: Apr 13, 2016 at 03:35 PM

Copyright 2017 Redgate Software. Privacy Policy