Delete nodes with certain value from XML using TSQL

Question

I'm trying to parse a piece of XML and remove nodes that contain certain values. I know how to remove them if the value is exact, but I want to use something like a "contains".

This works to delete exactly:

update @XML set data.modify('delete //Message[text() = "customer has deleted their account"]')

But I want to delete where the "Message" node just contains the text, something like:

update @XML set data.modify('delete //Message[contains(text(), "customer")]')

However this returns the error:

XQuery [@XML.data.modify()]: 'contains()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Phil · Accepted Answer · 2013-02-25 18:28:17Z

23

Try

update @XML set data.modify('delete //Message[text()][contains(.,"customer")]')

to delete the <message/> element and its contents

update @XML set data.modify('delete //Message//text()[contains(.,"customer")]')

to delete the <message/> element's contents.

Example here http://msdn.microsoft.com/en-us/library/ms178026.aspx

answered Feb 25, 2013 at 18:28

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Bowen Over a year ago

Prefect. I'd got to something very similar, but it was a 2 step. Delete the contents of the node, and then delete empty nodes. This is much more elegant.

Community · Accepted Answer · 2017-04-13 12:42:36Z

6

    declare @XML xml = '
    <Root>
      <Sub>
        <Record>
          <Guid>aslkjflaksjflkasjfkljsd</Guid>
        </Record>
        <Record>
           <Guid>opqiwuerl;kasdlfkjawop</Guid>
        </Record>
      </Sub>
    </Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

Reference https://dba.stackexchange.com/questions/40039/how-to-return-xml-node-ordinal-or-delete-node-based-on-element-value

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jan 12, 2017 at 11:11

Thalles Noce

8061 gold badge10 silver badges21 bronze badges

Collectives™ on Stack Overflow

Delete nodes with certain value from XML using TSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related