Syncing and updating a table via XML

Hello all,

I have a (odd?) requirement where I need to sync a table with an XML document. This is being done client side now via recrodsets and works fine but is too slow.

Given an XML document that represents a table, I need to manipulate a similar target table that matches the XML structure (but has additional fields) such that:

  1. Any rows in the table not in the XML are deleted
  2. Any rows in the XML that are not in the table are added to the table
  3. Any rows in the XML that match (via the PK columns in the XML) the table are updated.

The target table has additional columns that are not in the XML, so I can't delete the target table and rebuild it from the XML or data loss would occur.

I don't have an issue with the DELETE, INSERT, and UPDATE being done in three separate steps, or even moving the XML into a temporary table if that facilitates the process. With that in mind, I know how to move the XML into a table:

Given the source XML document @XML

<root>
  <row>
    <pkcol1>99</col1>
    <pkcol2>AAAA</vol2>
    <col3>Update this!</col3>
  <row>
  <row>..</row>
  <row>..</row>
  <row>..</row>
  and so on...
</root>

CREATE TABLE Source (
  pkcol1 INT,  --match this
  pkcol2 VARCHAR(30), --match this
  col3  VARCHAR(64),  -- update this!
);
INSERT INTO Source (pkcol1, pkcol2, col3)
SELECT DISTINCT
	'pkcol1' = x.v.value('pkcol1[1]', 'INT'),
	'pkcol2' = x.v.value('pkcol2[1]', 'VARCHAR(30)'),
	'col3' = x.v.value('col3[1]', 'VARCHAR(64)'),
FROM
	@XML.nodes('/root/row')x(v)

My target table has all the same named and typed columns as the XML plus additional columns that must be preserved.

An example target table:

CREATE TABLE Target (
  pkcol1 INT,  --match this
  pkcol2 VARCHAR(30), --match this
  col3  VARCHAR(64),  -- update this!
  col4  VARCHAR(64)  --preserve this!
  col5  VARCHAR(64)  --preserve this!
  col6  VARCHAR(64)  --preserve this!
 col_N ...and so on
);

Breaking the problem into DELETE, INSERT and UPDATE seems reasonable to me, and I think moving the XML into a temporary table would simplify things.

So given a temporary source table with columns that are a subset of the target table, how would I DELETE any rows in the target that are not in the source? (I'm sure this is wrong due to the multiple PKs)

DELETE FROM Target t
WHERE t.pkcol1, t.pkcol2 
NOT IN (SELECT s.pkcol1, s.pkcol2 FROM Source s)

The UPDATE (again, definitely wrong):

UPDATE Target t SET t.col3 = s.col3 WHERE t.pkcol1=s.pkcol1 AND t.pkcol2 = s.pkcol2

The INSERT

INSERT INTO Target t (t.pkcol1, t.pkcol2, t,col3, t.col4, t.col5) VALUES (s.pkcol1, s.pkcol2, s.col3, '','')

Any insights you can provide are greatly appreciated!

hi

please see this link

hope it helps :slight_smile: :slight_smile:

if at all possible move away from xml parameter to tvp, using user defined table types

CREATE TYPE [dbo].[budgetresourcesType] AS TABLE(
	[budgetid] [int] NOT NULL,
	[salarygraderoleid] [int] NOT NULL,
	[resourcecount] [int] NOT NULL,
	[resourcepercentage] [int] NOT NULL
)
GO

believe me I used to be an xmlphile ask @ScottPletcher. He made me a believer in TVP. But that said here is what you can do with this xml while you migrate towards tvp with

--DELETE but do you really want to do this??? it will wipe out everything.

DELETE FROM Target t
WHERE NOT exists
(SELECT 1 
   FROM Source src  
   where t.pkcol1 = src.pkcol1 and t.pkcol2 = s.pkcol2)

--UPDATE

UPDATE t
SET t.col3 = s.col3
      from Target t
       join Source s
       on t.pkcol1=s.pkcol1 AND t.pkcol2 = s.pkcol2

--INSERT

INSERT INTO Target t (t.pkcol1, t.pkcol2, t,col3, t.col4, t.col5) 
select s.pkcol1, s.pkcol2, s.col3, s.col4, s.col5
   from source s
where not exists(select 1 
                             from Target tgt 
                          where tgt.pkcol1 = s.pkcol1 
                               and tgt.pkcol2 = s.pkcol2
                              and tgt.col3= s.col3
                              and tgt.col4= s.col4
                              and tgt.col5= s.col5
)

In the DELETE you said it would wipe out everything. Well, it will wipe out only columns that don't meet the WHERE requirement, which is exactly what I want. The src is authoritative in my case, if it's not there it does not belong in Target. Am I missing something? And thanks for the heads up on TVP, I will look into it.

So if you have 10million rows in target and just 1 in source wipe it all off?