Insert and Update with OpenXML in SQL

This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.

 

declare @i int

 

exec sp_xml_preparedocument @i output, 

'<mydata>

  <test xmlID="3" xmlData="blah blah blah"/>

  <test xmlID="1" xmlData="blah"/>

</mydata>'

 

insert into test 

select xmlID, xmlData 

from OpenXml(@i, 'mydata/test')

with (xmlID int, xmlData nvarchar(30))

where xmlID not in (select xmlID from test)

 

update test

set test.xmlData = ox.xmlData

from OpenXml(@i, 'mydata/test')

with (xmlID int, xmlData nvarchar(30)) ox

where test.xmlID = ox.xmlID

 

exec sp_xml_removedocument @i