CODEWITHSUNDEEP
xmlt-sqlxpathxml.modify
Colin Dawson
Colin Dawson

Reputation: 465

delete nodes from xml efficiently

Here's a challange. The code below shows a working method of deleting some rows from an xml variable. The intention is to delete the rows which do not have the primaryKey attribute set to 1, where the oldValue and newValue attributes are the same. (by the same I mean, both can be null or both having the same value) The code below demonstrates this happening. However, the code below makes use of a cursor. It would not surprise me if it was possible to perform the delete using a single @xml.modify or at least without having to resort to using the cursor.

declare @xml xml ='<changes schemaName="Security" tableName="AccessProfiles">
  <column name="AccessProfileId" primaryKey="1" oldValue="114" newValue="114" />
  <column name="AccessProfileName" oldValue="test" newValue="Testing" />
  <column name="DeleteMeSame" oldValue="testValue" newValue="testValue" />
  <column name="DeleteMeNull" />
  <column name="KeepMePrimaryNull" primaryKey="1" />
  <column name="KeepMePrimarySame" primaryKey="1" oldValue="sameValue" newValue="sameValue"/>
</changes>';

declare @columnName sysname;

declare deleteNodesCursor cursor fast_forward for
    with shreddedXml as (
        select
            N.value( '@name' , 'sysname' ) as name,
            N.value( '@primaryKey' , 'bit' ) as primaryKey,
            N.value( '@oldValue' , 'varchar(max)' ) as oldValue,
            N.value( '@newValue' , 'varchar(max)' ) as newValue
        from @xml.nodes('/changes/column') as T(N)
    )
    select
        Name
    from shreddedXml
    where primaryKey is null
    and (oldValue = newValue
    or  (oldValue is null and newValue is null))

open deleteNodesCursor

while (1=1)
begin
    fetch next from deleteNodesCursor into @columnName
    if @@fetch_status != 0
        break;

    set @xml.modify( 'delete /changes[1]/column[@name= sql:variable("@columnName")]' )
end;

close deleteNodesCursor;
deallocate deleteNodesCursor;

select @xml

In summary, the question is, how can I accomplish this efficiently?

Upvotes: 1

Views: 302

Answers (1)

har07
har07

Reputation: 89285

"The intention is to delete the rows which do not have the primaryKey attribute set to 1, where the oldValue and newValue attributes are the same. (by the same I mean, both can be null or both having the same value)"

That can be translated into a single @xml.modify expression as follow (tested and worked in SQL Server 2008R2) :

set @xml.modify('delete /changes/column[not(@primaryKey=1) and not(@oldValue!=@newValue)]')

Upvotes: 1

Related Questions