SQL Server - xml type columns - XML DML

Question

I want to delete some nodes from menu xml that is being stored in a typed xml column in database.
Snippet from menu xml -

<menu xmlns="http://xxx" ..>
<menuItem name="Menu1">
    <menuItem name="SubMenu1">
        <role>role1</role>
        <url target="webPage1.aspx" />
    </menuItem> 
</menuItem>

I am trying to delete <role> under menuItem = "SubMenu1" using following XML DML -

UPDATE [dbo].[MenuTest]
SET xmlMenu.modify('
    declare namespace ns="http://xxx";
    delete(/ns:menu/ns:menuItem[@name="Menu1"]/ns:menuItem[@name="SubMenu1"]/ns:role[1])
')

But getting this error:

Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s):http://xxx:role where element 'http://xxx:url' was specified.

Can please guide what I am missing here.

Thank you!

score 1 · Accepted Answer · answered Dec 12 '11 at 14:39

1

It looks like your XML column is hooked up to a XML-schema that requires a <role> node before the <url> node.

What you have posted here works just fine.

declare @T table 
(
  xmlMenu xml
)

insert into @T values
('<menu xmlns="http://xxx">
     <menuItem name="Menu1">
       <menuItem name="SubMenu1">
         <role>role1</role>
         <url target="webPage1.aspx" />
       </menuItem> 
    </menuItem>
  </menu>')


UPDATE @T
SET xmlMenu.modify('
    declare namespace ns="http://xxx";
    delete(/ns:menu/ns:menuItem[@name="Menu1"]/ns:menuItem[@name="SubMenu1"]/ns:role[1])
')

Result

<menu xmlns="http://xxx">
  <menuItem name="Menu1">
    <menuItem name="SubMenu1">
      <url target="webPage1.aspx" />
    </menuItem>
  </menuItem>
</menu>

answered Dec 12 '11 at 14:39

Mikael Eriksson

136,425
22
210
281

Good to know :-) Can also please guide how can I delete node with text 'role1', so if I have multiple nodes, I want to delete only those having particular text values. – inutan Dec 12 '11 at 14:53
@iniki - just replace `ns:role[1]` with `ns:role[. = "role1"]` and it will delete all `` nodes with value `role1`. – Mikael Eriksson Dec 12 '11 at 14:58
Thanks for your reply, but I am getting this error - Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element(ns{xxx}:role,xs:anyType)'. – inutan Dec 12 '11 at 15:11
@inki - Works for me, try it here: http://data.stackexchange.com/stackoverflow/qt/121279/ – Mikael Eriksson Dec 12 '11 at 15:14
1

it's again seems some XML schema issue :( – inutan Dec 12 '11 at 15:17

SQL Server - xml type columns - XML DML

1 Answers1