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!

Mikael Eriksson · Accepted Answer · 2011-12-12 14:39:44Z

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, 2011 at 14:39

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

5 Comments

inutan Over a year ago

Good to know :-) Can also please guide how can I delete node with text 'role1', so if I have multiple <role/> nodes, I want to delete only those having particular text values.

Mikael Eriksson Over a year ago

@iniki - just replace ns:role[1] with ns:role[. = "role1"] and it will delete all <role> nodes with value role1.

inutan Over a year ago

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

Mikael Eriksson Over a year ago

@inki - Works for me, try it here: data.stackexchange.com/stackoverflow/qt/121279

inutan Over a year ago

it's again seems some XML schema issue :(

Collectives™ on Stack Overflow

SQL Server - xml type columns - XML DML

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related