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

Question

I have a table called XML (in SQL Server 2008) and it has a field called XmlDocument of type XML. I am trying to to delete an attribute from an XML variable.

Here is how my xml looks like

<clue_personal_auto xmlns="http://cp.com/rules/client">
  <admin>
     <receipt_date>03/16/2011</receipt_date>
     <date_request_ordered>03/16/2011</date_request_ordered>
     <report_usage>Personal</report_usage>
  </admin>
</clue_personal_auto>

My query

UPDATE XML
SET XmlDocument.modify('delete  (/clue_personal_auto/@xmlns)[1]')
 WHERE xmlid = 357

When I run this query in query analyzer I see the message "1 row(s) affected" but in reality the xmlns attribute of clue_personal_auto element is not being removed. Any idea what am I doing wrong.

Thanks BB

(1 row affected) simply means that you performed an update. If there were a timestamp column, it would have changed. Just as update tbl set id=id also succeeds, but does nothing — RichardTheKiwi
– RichardTheKiwi, Commented Mar 17, 2011 at 22:16
Do note tha SQL-Server function .modify() use a vendor's XML Data Modification Language — user357812
– user357812, Commented Mar 17, 2011 at 22:54

RichardTheKiwi · Accepted Answer · 2011-03-17 22:08:39Z

8

You need to use WITH xmlnamespaces, otherwise "/clue_personal_auto" does not match the NAMESPACED clue_personal_auto xmlns="..." node.

Not only that, you cannot actually remove a namespace since it is not a normal attribute.

Example of removing a regular attribute

declare @xml table (xmlid int, xmldocument xml)
insert @xml select 357, '
<clue_personal_auto xmlns="http://cp.com/rules/client" otherattrib="x">
  <admin>
     <receipt_date>03/16/2011</receipt_date>
     <date_request_ordered>03/16/2011</date_request_ordered>
     <report_usage>Personal</report_usage>
  </admin>
</clue_personal_auto>'

;WITH XMLNAMESPACES ('http://cp.com/rules/client' as ns)
UPDATE @XML
SET XmlDocument.modify('delete  (/ns:clue_personal_auto/@otherattrib)[1]')
WHERE xmlid = 357

select * from @xml

answered Mar 17, 2011 at 22:08

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

Comments

Peter Oehlert · Accepted Answer · 2011-03-17 22:14:45Z

6

UPDATE XML
  SET CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), XmlDocument), N' xmlns=...'))
WHERE ID = 357

answered Mar 17, 2011 at 22:14

Peter Oehlert

17.1k7 gold badges46 silver badges49 bronze badges

1 Comment

Ed Graham Over a year ago

Nice -- brute force but effective!

marc_s · Accepted Answer · 2011-03-17 22:10:16Z

2

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!

answered Mar 17, 2011 at 22:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

Ed Graham Over a year ago

One reason for getting rid of name-spaces is when they break a lot of legacy code that has never previously had to work with them. In my application, name-spaces serve no purpose (other than to make existing XPath commands stop working).

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related