Xml DML - delete error - Cannot implicitly atomize

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Xml DML - delete error - Cannot implicitly atomize

Author

Topic

iniki
Starting Member

1 Post

Posted - 2011-12-13 : 09:18:13

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!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-13 : 11:11:27

It has to be your schema that is doing it. Check to see if you have something like minOccurs="3" in your XML schema definition.

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-14 : 08:05:27

Ok man try below code

String _xmlData = "Xml comes here";
        
        XmlDocument _xdoc = new XmlDocument();
       
        _xdoc.LoadXml(_xmlData);//loading XML in xml doc

        if (_xdoc.HasChildNodes)
        {
            XmlNodeList nodeList = _xdoc.GetElementsByTagName("node");  //get node list         
            foreach (XmlNode node in nodeList)
            {
               _xdoc.DocumentElement.RemoveChild(node);
            }
        }

Also show below link
http://forums.asp.net/t/1470170.aspx/1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources