Optimise XML update in T-SQL

Question

With the error below in mind - any idea how I might optimize the code below?

The column name 'MyXmlColumn' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'MyXmlColumn' may appear twice in the view definition.

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''play'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''eat'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''work'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''kill'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''wash'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''home'']/text())[1] with "false"')

UPDATE #tempTable
SET MBO.modify('replace value of (/*[local-name()=''configuration'']/*[local-name()=''sleep'']/text())[1] with "true"'),    
    ToPayment  = 1

Here is the soultion: Here is the solution:: CREATE FUNCTION [dbo].[SBOObject] (
-- Add the parameters for the function here @var2 XML,
@var1 nvarchar(5), @var3 nvarchar(5),

) RETURNS XML AS BEGIN
SET @var2.modify('declare default element namespace "http://schemas.ObjectWorld"; replace value of (/configuration/sleep/text())[1] with sql:variable("@var1")') SET @var2.modify('declare default element namespace "http://schemas.ObjectWorld"; replace value of (/configuration/work/text())[1] with sql:variable("@var3")')

RETURN @var2

END

I've tried this and the best I've come across is a cursor based approach such as this: blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx. Alternately, you could shred the data out into a relational table, update it, then rebuild the xml, but that's ugly as well! — Katherine Elizabeth Lightsey
– Katherine Elizabeth Lightsey, Commented Sep 9, 2014 at 18:22
Hoi Elizabeth. Thanks for the Feedback. I have seen the logic. but it will not work for me because the elements I have are quite different. Thanks — Kingsley Schmid
– Kingsley Schmid, Commented Sep 10, 2014 at 6:33

Kingsley Schmid · Accepted Answer · 2014-09-10 12:49:28Z

0

CREATE FUNCTION [dbo].[SBOObject]

(
-- Add the parameters for the function here @var2 XML,
@var1 nvarchar(5), @var3 nvarchar(5),

) RETURNS XML AS BEGIN
SET @var2.modify('declare default element namespace "http://schemas.ObjectWorld"; replace value of (/configuration/sleep/text())[1] with sql:variable("@var1")') SET @var2.modify('declare default element namespace "http://schemas.ObjectWorld"; replace value of (/configuration/work/text())[1] with sql:variable("@var3")')

-- RETURN @var2

--END

answered Sep 10, 2014 at 12:49

Kingsley Schmid

618 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimise XML update in T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related