It's possible with separate updates but doesn't look like you can do it in one:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=279025&SiteID=1
DECLARE @xml XML
SET @xml = '
<email>
<recipients>
<address name = "Leon" email = "leon@dbpro_forum.com">
</address>
</recipients>
</email>'
DECLARE @tblTasks TABLE ( xmlEmail XML )
DECLARE @emails TABLE ( txtTEname VARCHAR(20), txtTEemail VARCHAR(30) )
INSERT INTO @tblTasks VALUES( @xml )
INSERT INTO @emails VALUES( 'Leon Mayne', 'leon.mayne@dbpro_forum.com' )
-- Separate updates
UPDATE t
SET xmlEmail.modify('replace value of (/email/recipients/address/@name)[1]
with sql:column("e.txtTEname")')
FROM @tblTasks t
CROSS JOIN @emails e
UPDATE t
SET xmlEmail.modify('replace value of (/email/recipients/address/@email)[1]
with sql:column("e.txtTEemail")')
FROM @tblTasks t
CROSS JOIN @emails e
SELECT *
FROM @tblTasks
I thought you might be able to do something with the XQuery if statement,
but couldn't get it to work.
I guess that makes sense from a SQL point of view, because what you're
original update it saying is (pseudo-code):
UPDATE t
SET (part of)col1 = value1
SET (another part of)col1 = value2
You wouldn't do that in SQL because you couldn't guarantee how the column
would end up.
HTH
wBob
"Leon Mayne" wrote:
> Hello,
> I'm trying to update multiple nodes in an XML column in one update
> statement but am getting the error:
>
> Column name 'xmlEmail' appears more than once in the result column list.
>
> The SQL I'm using is:
>
> UPDATE
> @tblTasks
> SET
> xmlEmail.modify('replace value of (/email/recipients/address/@name)[1]
> with sql:column("e.txtTEname")'),
> xmlEmail.modify('replace value of (/email/recipients/address/@email)[1]
> with sql:column("e.txtTEemail")'),
> xmlEmail.modify('replace value of (/email/subject)[1] with
> xs:string(concat("New Task Assignment: ", sql:column("lt.txtTTTdesc")))')
> FROM
> (etc)
>
> Does anyone know how to use replace for multiple nodes?
>
FAQ
Profile
Private Messages
Log in
