XML File to SQL Additive Insert

Question

I have a MSSQL table like such:

id     | name
-----------------------------
1        Gerald
2        Ron
3        Becky

And an XML document:

<People>
    <Person>
        <Name>Gerald</Name>
    </Person>
    <Person>
        <Name>Ron</Name>
    </Person>
    <Person>
        <Name>Becky</Name>
    </Person>
</People>

The primary key is ID, the XML document doesn't care about ID's, and Name's are unique.

The XML document changes, and my application should add new rows to the table for any <Person> not found in the SQL table.

Sure I can loop through the entire SQL table for each <Person> checking for a row and adding the row if it's not found, but is there a better way?

To add additional complexity, If I update a <Person>'s name in the XML file, it needs to update the same row that person was at before in the SQL table.

The client application that reads the XML file is in C#, and I'm using MSSQL to store the data. — Chris G.
– Chris G., Commented May 24, 2013 at 17:16

rs. · Accepted Answer · 2013-05-24 17:17:48Z

2

Try this

declare @data xml = '<People>
    <Person>
        <Name>Gerald</Name>
    </Person>
    <Person>
        <Name>Ron</Name>
    </Person>
    <Person>
        <Name>Becky</Name>
    </Person>
</People>'

--Convert xml to rows example:
SELECT N.value('.', 'varchar(50)') as value 
FROM @data.nodes('/People/Person/Name') as T(N)

-- use merge to find rows and insert when not found
Merge Table1 as tgt
using (
        SELECT N.value('.', 'varchar(50)') as Name 
        FROM @data.nodes('/People/Person/Name') as T(N)
      ) src
on (tgt.name = src.name)
when not matched then
insert (name)
values (src.name);

To update row, you need some kind of id in file to identify row to update

answered May 24, 2013 at 17:17

rs.

27.6k13 gold badges71 silver badges93 bronze badges

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

Comments

qewqew qdqw d · Accepted Answer · 2013-05-24 17:15:28Z

1

You could add an attribute to <Person>, which indicated that it was already in the database:

<People>
    <Person inserted="1">
        <Name>Gerald</Name>
    </Person>
    <Person inserted="1">
        <Name>Ron</Name>
    </Person>
    <Person inserted="0">
        <Name>Becky</Name>
   </Person>
</People>

answered May 24, 2013 at 17:15

qewqew qdqw d

1892 gold badges2 silver badges9 bronze badges

1 Comment

Chris G. Over a year ago

So if it's a fresh XML document, load the SQL database with the XML elements, then write a new XML document containing the inserted ID's. On an updated XML document, if one of the people changes, add changed="Yes" and import only those, using the inserted ID's, and write a new XML document without the changed attribute. Would that work?

Collectives™ on Stack Overflow

XML File to SQL Additive Insert

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related