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.
| Author |
Topic |
|
kumar_j
Starting Member
7 Posts |
 Posted - 2007-10-18 : 16:23:27
|
| Hi ,CREATE TABLE KUMAR ( ID INT PRIMARY KEY, Document XML(CONTENT KUMAR)) --Document is the xmlcolumn---------------------------------------CREATE XML SCHEMA COLLECTION KUMAR AS 'schema file to be placed'--KUMAR is the XML-Schema collection name--------------------------------------------INSERT KUMAR VALUES (1,'XML FILE TO BE PLACED')When I try to update the xml data by using the below queryUPDATE KUMAR SET Document.modify('replace value of (/X12_U1_837/UserId) with "ABC"')--(/X12_U1_837/UserId) Contains the XYZ,Which i need to replace with ABCI am getting the following errorXQuery [KUMAR.Document.modify()]: The target of 'replace' must be at most one node, found 'element(UserId,#anonymous) *' |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-04-05 : 00:47:15
|
Hopefully you solved this a long time ago, but in case anyone else finds this post...From BOL:quote:
Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a statical singleton. If the XML is typed, the type of the node must be a simple type. If multiple nodes are selected, an error is raised. If Expression1 returns an empty sequence, no value replacement occurs and no errors are returned. Expression1 must return a single element that has simply typed content (list or atomic types), a text node, or an attribute node.
Your code is not guaranteed to return a single node, you have to qualify it like thisUPDATE KUMAR SET Document.modify('replace value of (/X12_U1_837/UserId[1]/text()) with "ABC"')Again, from BOLquote:
Note that the target being updated must be, at most, one node that is explicitly specified in the path expression by adding a "[1]" at the end of the expression.
|
 |
|
|
|
|
|
|
|
Replace the xml node