Xml DML - delete error - Cannot implicitly atomize or apply 'fn:data()'

Question

Post reply

Xml DML - delete error - Cannot implicitly atomize or apply 'fn:data()'

inuts

SSC-Addicted

Points: 494
More actions
December 13, 2011 at 7:00 am

#249599

Hello there,
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>
<role>role2</role>
<role>role3</role>
<url target="webPage1.aspx" />
</menuItem>
</menuItem>
</menu>
I am trying to delete `<role>role1</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[. = "role1"])
')
But getting this error:
Msg 9314, Level 16, State 1, Line 4
XQuery [modify()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements,
found type 'xs:anyType' within inferred type 'element(ns{xxx}:role,xs:anyType)'.
Because Xml Schema is associated with xmlMenu column, I am not sure if I am calling Delete the right way.
Can please guide what I am missing here.
Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Works just like a charm here:

use tempdb;

go

create table MenuTest (

xmlMenu xml

);

insert into MenuTest values ('<menu xmlns="http://xxx">

<menuItem name="Menu1">

<menuItem name="SubMenu1">

<role>role1</role>

<role>role2</role>

<role>role3</role>

<url target="webPage1.aspx" />

</menuItem>

</menu>');

update MenuTest

set xmlMenu.modify('declare namespace ns="http://xxx";

delete(/ns:menu/ns:menuItem[@name="Menu1"]/ns:menuItem[@name="SubMenu1"]/ns:role[. = "role1"])

')

select * from MenuTest

drop table MenuTest

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

inuts SSC-Addicted Points: 494 More actions · Answer 2

Thanks for you reply.

But why I am getting this error, is it because schema is associated with 'xmlMenu' column?

okbangas SSChampion Points: 11773 More actions · Answer 3

Honestly, I don't know. But, I suspect that you may have a typed xml column, and that the error is related to the schema in one way or another. Does my code run without any problems on your system as well?

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

inuts SSC-Addicted Points: 494 More actions · Answer 4

yes, I had already tried that on an untyped xml column but mine is typed here 🙁

Mark Cowne One Orange Chip Points: 26968 More actions · Answer 5

inuts (12/13/2011)
yes, I had already tried that on an untyped xml column but mine is typed here 🙁

Can you post the schema?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537