Trouble inserting multiple nodes during UPDATE

I am trying to insert multiple nodes to an xml column using an update statement from a joined table. For (a simplified) example:

UPDATE
    Documents
SET
    DocumentDetails.modify('insert <DocumentParty name="{sql:column(''n.Name'')}"/> ...')
FROM
    Documents d
    INNER JOIN DocumentNameXRef dnx
       ON d.DocId=dnx.DocId
    INNER JOIN Names n
       ON dnx.NameId=n.NameId

Each document has multiple "party" names that I am trying to insert to the DocumentDetails xml column on the Documents table. When I run this statement only one name is inserted for each document, regardless of how many are actually produced by the join. Is there a way to make this work

Jay

Answer this question

Trouble inserting multiple nodes during UPDATE

S. Craig

Hi Jay.

Thanks and that's really not too bad.

Best regards
Michael
marcelv
Thanks for the reply.

Since this was just a one-time thing to fix some data (and the suggested route seemed like too much effort), I just went with a cursor. I was able to insert 623k nodes into 250k rows in about 25 minutes, which wasn't as bad as I thought it was going to be.

Jay
Neo2000

This does not work in this way. Your SET operation will be applied for one join row at a time and since it is done declaratively, it will only be applied once to each row.

This is similar to the following case:

create table T1(id int, val nvarchar(20))
go

insert into T1
select 1, N'a'
union
select 2, N'b'
go

create table T2(t2id int, t1id int, t2val nvarchar(20))
go

insert into T2
select 11, 1, N'aa'
union
select 12, 1, N'ab'
union
select 21, 2, N'ba'
union
select 22, 2, N'bb'
go

update T1
set T1.val = T1.val + T2.t2val
from T1 inner join T2 on T1.id = T2.t1id

select * from T1

You may be better off, replacing the whole document with one that uses FOR XML to compose the new document by aggregating the new information and composes it with the old one. If you can post some more concrete example data, we may be able to provide you with the appropriate solution.

Best regards

Michael
Trouble inserting multiple nodes during UPDATE

Answer this question