May 7, 2008 at 4:34 pm
I am attempting to perform an update to an xml column (AttributeList). What my update needs to do is add a leading character (3) to an already existing string (Account_number_1). My code is as follows:
UPDATE tbl1
SETaccountNumber = '3' + accountNumber,
AttributeList.modify
('replace value of (/AttributeList/Account_number_1/text())[1] with xs:text("3" + Account_number_1)')
WHEREClientAccountId = @ClientAccountId
When I attempt to execute the query I receive the following error:
Msg 2214, Level 16, State 1, Procedure ModifyAcctNum, Line 28
XQuery [tbl1.AttributeList.modify()]: The type 'text' is not an atomic type
Can someone please help me with this as I am new to the xquery world and would greatly aapreciate the assistance.
Thank You!
May 8, 2008 at 9:39 am
For those of you who might need some kind of help regarding this issue in the future, here is the solution we managed to figure out:
UPDATE tbl1
SETaccountNumber = '3' + accountNumber,
AttributeList.modify('replace value of (/AttributeList/CustodianRawData/dbo.tblAcct/Account_number_1/text())[1] with concat("3", string((/AttributeList/CustodianRawData/dbo.tblAccount_Registration_Data_Detail/Account_number_1)[1]))')
WHEREsomelogic
ANDsomemorelogic
It was a matter of including a full path and a syntax correction in the concatenation.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply