In SQL Server, how would you extract:




from a sql column of type xml with value:








and insert (the above extraction) into another sql column of type xml
with current value:






for a result of:

I figured out how to extract the nodes:

SELECT NewTable.Member.query('.')
FROM table1 CROSS APPLY xml_data.nodes('/group/member') AS
NewTable(Member)
WHERE pk_column_id = 1 AND Member.value('@member_id', 'int') NOT IN
(SELECT ref.value('@member_id', 'int') FROM table2
CROSS APPLY
sync_data.nodes('/group/member') AS node(ref) WHERE pk_column_id = 1)

Now I just need to know how to update table2 with the results...

You could use XML DML insert statement, although in 2005 it is limited. You
would do better to grab the member_id scalar value using the .value() method
and use that to insert new nodes in table2 with XML DML if you decide to go
the XML DML route.

gmail.com> wrote in message
news:f03870e2-ad03-4a80-ba56-6384d6fa428c@b29g2000hsa.googlegroups.com...

Thanks, Mike.

This works for 1 node:

DECLARE @insertStmt nvarchar(MAX)
DECLARE @insertXML nvarchar(MAX)
SELECT @insertXML = coalesce(@insertXML + '', '') +
convert(varchar(max),NewTable.Member.query('.'))
FROM table1 CROSS APPLY xml_data.nodes('/group/member') AS
NewTable(Member)
WHERE pk_column_id = 37 and Member.value('@member_id', 'int') NOT IN
(SELECT ref.value('@member_id', 'int') FROM table2 CROSS APPLY
sync_data.nodes('/group/member') AS node(ref) WHERE pk_column_id
= 16)
SET @insertStmt = 'UPDATE table2 SET sync_data.modify(''insert ' +
@insertXML + ' before (/group/member[1])[1]'') WHERE pk_column_id =
16'

exec sp_executesql @insertStmt

...but not for multiple nodes. I guess I'll have to use a cursor and
insert one node at a time...

Yes, SQL 2005 XML DML is not as powerful as 2008. They've added support for
XML variables/columns in 2008. Here's something I would think should work,
but for some reason doesn't:

UPDATE table2 -- modify method *should* insert a new node for each
@member_id attribute,
-- but it stops after one for some reason
SET Member.modify('insert
before (/group/member)[1]')
FROM ( -- Subquery returns all @member_id attributes from the source table
SELECT x.value('(@member_id)[1]', 'INT') AS member_id
FROM table1
CROSS APPLY table1.Member.nodes('/group/member') T(x)
WHERE pk_column_id = 37
) z
WHERE table2.pk_column_id = 16;

I'm probably overlooking something obvious, but this update with the XML DML
statement only inserts a single node from table1 to table2, when it should
be inserting them all. I'm probably missing something really obvious
here...

gmail.com> wrote in message news:18e074a1-d096-4b2e-8
01b-a6217175e194@e10g2000prf.googlegroups.com...

Here is a TSQL statement that can merge two instances. note that the
original example does not say how to corelate more than one row between the
two tables. So that code is missing. The follow expression should be usable
in an update source expression with the necessary adjustments for syntax and
update correclation:

WITH T1mid as (
SELECT m.value('@member_id', 'int') as mid
FROM tab1 cross apply x.nodes('/group/member') as N(m)
),
T2mid as (
SELECT g.value('@group_id', 'int') as gid, m.value('@member_id', 'int'...

Here is an update statement that should work (only tested with one XML
instance in each column):

WITH T1mid as (
SELECT m.value('@member_id', 'int') as mid
FROM tab1 cross apply x.nodes('/group/member') as N(m)
),
T2mid as (
SELECT g.value('@group_id', 'int') as gid, m.value('@member_id', 'int'...

So I just gotta ask - do you see the problem with the sample I posted? It
seems like it should work, but it doesn't. Unless I'm missing something
obvious... Thanks

"Michael Rys [MSFT]" online.microsoft.com> wrote in message
news:F2A54444-B881-4798-AA8A-0B9BA22001B2@microsoft.com...

...