Delete xml node from SQL Server 2008 based on attribute value

Question

I have a xml structure in my database like so:

<ArrayOfContactDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ContactDetails id="93679d1d-9feb-45d1-8356-e85d188fa34c">
    <contactid>93679d1d-9feb-45d1-8356-e85d188fa34c</contactid>
    <contactname>Name 1</contactname>
    <contactemail>Email 1</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="69f54067-edf9-414e-80b6-099ac471dc43">
    <contactid>69f54067-edf9-414e-80b6-099ac471dc43</contactid>
    <contactname>Name 2</contactname>
    <contactemail>Email 2</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="93144086-be1c-4f34-a5f7-6e8ac67c2121">
    <contactid>93144086-be1c-4f34-a5f7-6e8ac67c2121</contactid>
    <contactname>Name 3</contactname>
    <contactemail>Email 3</contactemail>
    <contactphonenumber>123456</contactphonenumber>
  </ContactDetails>
</ArrayOfContactDetails>

And I'm trying to delete a ContactDetails node based on the ContactDetails id attribute. But I seem to be running into a brick wall.

My SP code is like so

UPDATE tableName 
SET tableField.modify('delete //ContactDetails[@id=sql:variable("@contactId")]') 
WHERE tableId = @tableId 

I get no errors on the page or when debugging/executing the sp and its just driving me insane as to why its not working!!

Thanks, Tom

Answer 1

What is your @contactID variable defined as??

It works on my machine :-) Try this:

DECLARE @work TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @work VALUES(1, '<ArrayOfContactDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ContactDetails id="93679d1d-9feb-45d1-8356-e85d188fa34c">
    <contactid>93679d1d-9feb-45d1-8356-e85d188fa34c</contactid>
    <contactname>Name 1</contactname>
    <contactemail>Email 1</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="69f54067-edf9-414e-80b6-099ac471dc43">
    <contactid>69f54067-edf9-414e-80b6-099ac471dc43</contactid>
    <contactname>Name 2</contactname>
    <contactemail>Email 2</contactemail>
    <contactphonenumber>123234234</contactphonenumber>
  </ContactDetails>
  <ContactDetails id="93144086-be1c-4f34-a5f7-6e8ac67c2121">
    <contactid>93144086-be1c-4f34-a5f7-6e8ac67c2121</contactid>
    <contactname>Name 3</contactname>
    <contactemail>Email 3</contactemail>
    <contactphonenumber>123456</contactphonenumber>
  </ContactDetails>
</ArrayOfContactDetails>')

DECLARE @contactID VARCHAR(50) = '69f54067-edf9-414e-80b6-099ac471dc43'

UPDATE @work 
SET XmlContent.modify('delete //ContactDetails[@id=sql:variable("@contactId")]') 
where id = 1

SELECT * FROM @Work WHERE id = 1

The resulting XML I get back comes out without that one <ContactDetails> node.

Is your @contactID defined as a UNIQUEIDENTIFIER by any chance?? You'd have to convert that to a varchar - the XML manipulation stuff all works with strings....

PS: another thing I just noticed - this won't work either:

DECLARE @YourOriginalContactID UNIQUEIDENTIFIER 
SET @YourOriginalContactID = '69f54067-edf9-414e-80b6-099ac471dc43'

DECLARE @ContactID VARCHAR(50)
SET @ContactID = CAST(@YourOriginalContactID AS VARCHAR(50))

This fails because the CAST operations converts the GUID to an UPPERCASE string..... you need to turn it into lower case again:

SET @ContactID = LOWER(CAST(@YourOriginalContactID AS VARCHAR(50)))

THEN it works again! Pretty tricky....