Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Xquery issue attempting to update an attribute Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2008 4:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2008 11:23 AM
Points: 6, Visits: 20
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
SET accountNumber = '3' + accountNumber,
AttributeList.modify
('replace value of (/AttributeList/Account_number_1/text())[1] with xs:text("3" + Account_number_1)')
WHERE ClientAccountId = @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!
Post #496775
Posted Thursday, May 8, 2008 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2008 11:23 AM
Points: 6, Visits: 20
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
SET accountNumber = '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]))')
WHERE somelogic
AND somemorelogic

It was a matter of including a full path and a syntax correction in the concatenation.
Post #497208
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse