Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have an XML document similar to this:

<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>

I am replacing an entire <Record> node based on some criteria. The <Record> nodes contain a <Guid> that I can use to identify them (pretend those are valid GUIDs please!).

Knowing the GUID, I am returning that node's XML into a variable for further processing. Downstream, I need to be able to delete that particular node so I that I can insert the modified version of the node back into the original document.

Is there a way to determine the ordinal, or another way to use a delete/replace method to remove a node based on an element's value?

share|improve this question
1  
About getting the ordinal value from the XML you can have a look at this answer and a blog post by Adam Machanic. Uniquely Identifying XML Nodes with DENSE_RANK – Mikael Eriksson Apr 15 at 20:22

1 Answer

up vote 3 down vote accepted

If you have the value in a variable you can use sql:variable() in the predicate for the delete.

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")]')

Replacing a record with a new record would look something like this.

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

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
<Record>
  <Guid>aslkjflaksjflkasjfkljsd</Guid>
  <Value>some new value</Value>
</Record>'

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

First add the new record after the existing one and the delete the first occurrence where Guid is a match. Without [1] in the delete you will delete all occurrences, not only the first one.

share|improve this answer
1  
I was hoping you would get the XML batsignal I sent up! Thanks, I'll upvote now and accept once I confirm it! – JNK♦ Apr 15 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.