How to Delete an Attribute from an Xml Variable in SQL Server 2008

How to delete an attribute from an XML variable in sql server 2008?

I can't seem to find an easy way to do this - but the real question remains: why do you want to remove the namespace?? Using the WITH XMLNAMESPACES ... construct, you can easily make use of the namespaces.

Instead of putting a lot of effort in getting rid of it - learn about XML namespaces and start using them!

You can quite easily use that XML namespace in your queries:

;WITH XMLNAMESPACES (DEFAULT 'http://cp.com/rules/client' )
SELECT
    XmlDocument.value('(/clue_personal_auto/admin/report_usage)[1]', 'varchar(25)')
FROM XML
WHERE ID = 357

and be happy with it - no need to artificially remove xmlns= declarations anymore!

Delete xml node from SQL Server 2008 based on attribute value

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....

Delete xml node from SQL Server 2008 r2 table column based on attribute value

Well, since your XML attribute id is the XML namespace with the prefix p5, you must also declare and use that second XML namespace in your code:

declare @lookupvalueId varchar(50)
declare @ruleId varchar(50)

set @lookupvalueId = 'b897f3ac-b40f-4b96-b438-eb156a26457e'
set @ruleId = 'fc39f423-05c0-4de9-ae46-12fe3c0c279b'

;WITH XMLNAMESPACES ('urn:gjensidige:processguide:201201' as qn,
                     'urn:gjensidige:processguide:201201' as p5)
update 
    pdr_processdefinitionrule
set 
    PDR_RuleXml.modify('delete (/qn:DifficultyRule/qn:FromControls/qn:Control/qn:FilterValues/qn:FilterValue[@p5:Id=sql:variable("@lookupvalueId")])')
where 
    pdr_guid = @ruleId

See that second XML namespace definition for p5? See the use of that XML namespace in the qn:FilterValue[@p5:Id=sql:variable("@lookupvalueId")] expression?

sql server xml delete two attributes at the same time

UPDATE TABLE SET
  xmlField.modify('delete (/clue_personal_auto/@*[local-name()=("attr1", "attr2")])')
 WHERE compare = 357

SE-Data

Delete XML node from in SQL Server 2008

Your UPDATE statement has no WHERE clause - do you really want to run this UPDATE against every single row in your table?

The trouble occurs if you have a row in your table that contains NULL in your Rates XML column. Therefore - you must check for NULL to avoid this error:

update tablename
set Rates.modify('delete (/rts/rt[@eId=sql:variable("@var")])[1]')
where Rates is not null

Updating Xml attributes with new values in a SQL Server 2008 table

From the early versions of your question it looks like your XML actually is on different rows in a table. If that is the case you can use this.

update YourTable set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

Working sample using a table variable.

declare @T table(XMLText xml)

insert into @T values('<Identification Name="John"  Family="Brown"   Age="30" />')
insert into @T values('<Identification Name="Smith" Family="Johnson" Age="35" />') 
insert into @T values('<Identification Name="Jessy" Family="Albert"  Age="60" />')
insert into @T values('<Identification Name="Mike"  Family="Brown"   Age="23" />')
insert into @T values('<Identification Name="Sarah" Family="Johnson" Age="30" />')

update @T set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

select *
from @T 

Delete xml node from SQL Server 2008 table column based on element value

You need to do this instead:

 ;WITH XMLNAMESPACES ('urn:gjensidige:processguide:201201' as qn)
 update 
     pdr_processdefinitionrule
 set 
     PDR_RuleXml.modify('delete (/qn:ControlVisibilityRule/qn:ToControls/qn:Control/qn:Id[text()=sql:variable("@Id")])')
 where 
     pdr_guid = @ruleId   

Basically, you want to delete the qn:Id element where the text() (the text that XML element contains) matches your variable @Id.

How to update an xml attribute value in an xml variable using t-sql?

set @xml.modify('replace value of (/a/@abb)[1] with 344')

Read more about it here. XML Data Modification Language (XML DML)

---

Related Topics