CODEWITHSUNDEEP
c#sql-servert-sql
Chris G.
Chris G.

Reputation: 3981

XML File to SQL Additive Insert

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.

Upvotes: 2

Views: 133

Answers (2)

rs.
rs.

Reputation: 27467

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

Upvotes: 2

qewqew qdqw d
qewqew qdqw d

Reputation: 189

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>

Upvotes: 1

Related Questions