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
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
UPDATE XML
SET CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), XmlDocument), N' xmlns=...'))
WHERE ID = 357
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With