need help on Updating XML node values

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 2005 Forums

Transact-SQL (2005)

need help on Updating XML node values

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-10-06 : 10:41:26

Hi

I am passing a xml string as an input parameter to sp.

XML string is :
<Patient>
<PatientID>1000230</PatientID>
<FirstName></FirstName>
<MiddleName></MiddleName>
<LastName></LastName>
<Address1></Address1>
<Address2></Address2>
</Patient>

In the sp i am updating the some of the XML nodes like

update [tablename]
set [columnname].modify('replace value of (/Patient/PatientID/text())[1] with sql:variable("@newpatientid")')

at the time of updating its showing the following error.

"UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL'. Verify that SET options are correct for use with indexed views
and/or indexes on computed columns and/or query notifications and/or xml data type methods."

after that i have gone thru the blogs and found some solutions. There they have mentioned need to ON the
quoted_identifier and concat_null_yields_null'

After that also still i am getting the below error

Code:

SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON

update [tablename]
set [columnname].modify('replace value of (/Patient/PatientID/text())[1] with sql:variable("@newpatientid")')

SET QUOTED_IDENTIFIER OFF
SET CONCAT_NULL_YIELDS_NULL OFF

error:

"UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or
query notifications and/or xml data type methods."

Help me out from this friends..

Thanks

Jack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources