How To: Insert and Update with OpenXML

Friday, November 20, 2009

How To: Insert and Update with OpenXML (39864 Requests)

I would like to update existing record if the ID exists in the table, otherwise insert the record(node) into the table..

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

So you can use the same openxml pointer to do an update and an insert.

Feedback

# re: How To: Insert and Update with OpenXML 11/19/2003 8:52 AM A Mitchell

Is there a more efficient way of updating multiple fields at once from the same XML "recordset", without having to use OpenXML n times ...

# re: How To: Insert and Update with OpenXML 11/20/2003 3:39 PM fernando.olmos@alcoa.com.au

Is there a way to do this using XMLBulkLoad instead and XSD?

Thanks

# re: How To: Insert and Update with OpenXML 12/1/2003 5:05 PM Bryant

You can update multiple fields at once, the example is simplified.

Fernando, I don't know of anyway to do this with BulkLoad.

# re: How To: Insert and Update with OpenXML 12/11/2003 1:31 AM jordi

really usefull and easy... thanks a lot

# re: How To: Insert and Update with OpenXML 3/12/2004 11:18 AM Bryan

I'm passing a EmplyeeXML with ID and Amount.. to a stored Procedure

I would like to update all employee's amount at the same time if it is not already updated.. if any one fails i should be able to rollback.... the entire batch..

Can you help me to do this..

# re: How To: Insert and Update with OpenXML 10/15/2004 7:56 AM Todd

I am trying to insert records, which I can do, the problem I have is I have multiple <test> nodes with no unique Identifier. When I do my insert, I get the correct number of rows inserted, but with the same value, from the first record found.

Any help

# how to insert XMLdata in VB 2/13/2005 10:58 PM Ravi Meduri

I would like to have an efficient way to insert XMLdata which comes as a record set(which I'm saving it as XMLdata) into a database along with other columns into a SQL table. The XMLData will be aroung 3 MB or 4MB of size. I wanted the solution in Visual Basic

# re: How To: Insert and Update with OpenXML 2/15/2005 4:47 AM MadhavRao

If we try Exists clause in WHERE clause it dosent work is it bug in SQL Server 2000. In above query if we write

insert into test
select xmlID, xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) as Dest
where not EXISTS (select xmlID from test as SRC WHERE Dest.xmlID = SRC.xmlID)

# re: How To: Insert and Update with OpenXML 3/9/2005 8:30 AM guntur

Have you guys ever run to a problem where the query timed out when you try to bulk insert 10000+ records using openxml?

# re: How To: Insert and Update with OpenXML 3/9/2005 8:31 AM guntur

btw, it takes 1 second to insert 1000 records, and 2 seconds for 2000. But when i attempt to insert 10000 records, it timed out after few seconds even when i set the timeout duration to 60 seconds.

# re: How To: Insert and Update with OpenXML 7/16/2005 1:46 PM Jose

hi, I have insert in a multiline text column,
I use a XML ntext parameter and OPENXML, but CHAR(13) is omitted.
I test with several encodings but same happen.
Please help or an explanation.

Note: I have a lot of procedures and I wouldnt change all.
Thanks.

# re: How To: Insert and Update with OpenXML 7/16/2005 2:04 PM Jose

# re: How To: Insert and Update with OpenXML 8/11/2005 12:03 PM Cobus

Is it possible with the update statement not to have to specify SET and the columns names to update..? Something like:
update test
from OpenXml(@i, 'mydata/test')
with test
where test.MemberID = ox.MemberID I will appreciate a response.

# re: How To: Insert and Update with OpenXML 11/5/2005 9:54 AM TECH

Can it is possible to directly insert data from XML to database?
I have problem in doing this.Thanks in advance.

# re: How To: Insert and Update with OpenXML 12/1/2005 11:57 AM Abhishek

my XML document is in this format
<SportsScheduleApi>
<SportType>
<type>College Football</type>
<Schedule>
<team_name1>Fresno St.</team_name1>
<team_name2>Louisiana Tech</team_name2>
<game_date>2005-12-02</game_date>
<game_start>12-02-2005 9:00 pm</game_start>
</Schedule>
</SportType>
</SportsScheduleApi>

i need to insert team_name1,team_name2, game_date & game_start into a table which has columns with the name TeamName1,TeamName2,GameDate & GameStart. How do I do it?

This is the SP:

ALTER PROC uspW3TeamList
@doc varchar(5000)
AS
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

INSERT SportsSchedule (TeamName1,TeamName2,GameDate,GameStartTime)
SELECT FROM OPENXML (@idoc, '/SportsScheduleApi/SportType[type=*]/Schedule')
WITH SportsSchedule(TeamName1 varchar(50),TeamName2 varchar(50),GameDate datetime,GameStart datetime)
EXEC sp_xml_removedocument @idoc
END
GO

Then I test it using
DECLARE @doc as varchar(5000)
SET @doc='<SportsScheduleApi><SportType><type>College Football</type><Schedule><team_name1>Fresno St.</team_name1><team_name2>Louisiana Tech</team_name2><game_date>2005-12-02</game_date><game_start>12-02-2005 9:00 pm</game_start></Schedule></SportType></SportsScheduleApi>'
EXEC uspW3TeamList @doc

I am not able to insert? please help.

# re: How To: Insert and Update with OpenXML 12/1/2005 12:25 PM Abhishek

# re: How To: Insert and Update with OpenXML 3/1/2006 1:50 AM Bug$

alter proc dbo.usp_IScale(@ScaleID int out,@SchoolID int , @AcademicYearID int ,@xmlData varchar(1000))
as
DECLARE @idoc int

BEGIN TRANSACTION
delete from Scale where SchoolID = @SchoolID

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData
/* Fields of Scale Table
SchoolID, AcademicYearID,NumberFrom,NumberTo, LiteralFrom, LiteralTo, CreationDate, UpdateDate

Data stored into XML

"<ROOT><DATA FROM=\"90\" TO=\"100\" LF=\"5\" LT=\"6\" GRADE=\"A \" /><DATA FROM=\"80\" TO=\"90\" LF=\"4\" LT=\"5\" GRADE=\"B\" /><DATA FROM=\"70\" TO=\"80\" LF=\"2\" LT=\"4\" GRADE=\"C\" /><DATA FROM=\"60\" TO=\"70\" LF=\"1\" LT=\"2\" GRADE=\"D\" />"
*/

INSERT INTO Scale
select @SchoolID, @AcademicYearID,[FROM],[TO],LF,LT,GRADE, GetDate(), null
FROM OPENXML (@idoc, '/ROOT/DATA',1)
WITH ([FROM] int, [TO] int, LF decimal, LT decimal,GRADE varchar(2))

EXEC sp_xml_removedocument @idoc
COMMIT TRANSACTION

SELECT @ScaleID = @@IDENTITY;

GO

# re: How To: Insert and Update with OpenXML 7/18/2006 3:46 AM radhika

ur solution is excellent......

but can v have any better way
without using the set column names in the update statement ,
v want the solution like insert .
it is directly performing insert without using any column names

# re: How To: Insert and Update with OpenXML 7/18/2006 3:47 AM radhika

# re: How To: Insert and Update with OpenXML 8/11/2006 3:37 AM Manu

i tried inserting in to a table from XML file..but only null values are getting updated

manu

# re: How To: Insert and Update with OpenXML 8/11/2006 3:52 AM Manu

i tried inserting in to a table from XML file..but only null values are getting updated

manu

# re: How To: Insert and Update with OpenXML 9/21/2006 3:25 AM dd

# ijlz kdigoszm 1/1/2007 10:33 AM nqzkpj@mail.com

rfag xdpreyz uozypqlfm regbxj mxyrdav ymxk ibpmz

# re: How To: Insert and Update with OpenXML 2/16/2007 6:09 AM re: How To: Insert and Update

BBBBBBBBBBBBBBBBBBBB

# re: How To: Insert and Update with OpenXML 3/1/2007 1:59 AM Satya

to insert into the table in openxml how to dynamically mention the column name and table name. i am passing column name and table name as a parameter to the procedure.but hoe to use that parameter value in place static column name. Thanka response

# re: How To: Insert and Update with OpenXML 3/1/2007 2:00 AM Satya

to insert into the table in openxml how to dynamically mention the column name and table name. i am passing column name and table name as a parameter to the procedure.but how to use that parameter value in place static column name. Thanks response

# ferdsc qlnbcy 3/13/2007 2:50 AM csqd@mail.com

pqmhglau rijksmpb ubcvlq wsrdcnqt kvwznh xgol bksmypc

# re: How To: Insert and Update with OpenXML 3/22/2007 11:16 AM Sherry

I am a newbie to XML development. What I need is to populate multiple rows a table with columns like this:

contentName varchar(50), content NTEXT

The value of ’content’(NTEXT) in table has to be stored as a XML string, e.g.,

<?xml version="1.0" encoding="UTF-8"?> <email template="102091_100005_250968" emailAddr="xxx.yyy@verizon.net"><FirstName>xxx</FirstName><LastName>yyy</LastName></email>

I was thinking to pack the data into a XML doc and insert multiple records to table with sp_xml_preparedocument:

declare @idoc int
DECLARE @doc varchar(2000)
SET @doc ='
<mydata>
<test contentName="57600_2_297738" content=”<?xml version="1.0" encoding="UTF-8"?><email emailAddr="xxx.yyy@verizon.net"><FirstName>xxx</FirstName><LastName>yyy</LastName></email>”/>
<test contentName="67600_3_297738" content=”<?xml version="1.0" encoding="UTF-8"?><email emailAddr=" xxx1.yyy1@verizon.net"><FirstName>xxx1</FirstName><LastName>yyy1</LastName></email>”/>
</mydata>'
EXEC sp_xml_preparedocument @idoc OUTPUT,@doc
SELECT contentName, content
FROM OPENXML(@idoc, '/mydata/test', 1)
with (contentName varchar(50), content NTEXT )

I got XML parse error in the first place, then I replace all the special characters with valid XML string as follows:
Content=”&lt?xml version=&quot1.0&quot encoding=&quotUTF-8&quot?&gt &ltemail emailAddr= &quot xxx.yyy@verizon.com &quot &gt&ltFirstName&gt &lt xxx/FirstName&gt &lt LastName&gt yyy&lt /LastName&gt &lt /emai

However it still complains about parse error... Is “NTEXT” causing trouble? Could someone please h

# re: How To: Insert and Update with OpenXML 3/22/2007 11:25 AM Sherry

# re: How To: Insert and Update with OpenXML 3/27/2007 1:37 PM Anil

i have an XML file which contains as shown below
<EmployeeTable>
<Employee ID="1" Name="aaa"/>
<Employee ID="2" Name="aaa" Sal="12000"/>
</EmployeeTable>

Now i want to update both employee Ids 1 and 2.
Iam facing the problem if iam using OpenXML it is inserting null sal for Id 1.

Below is my code:
Update Employee
set Employee.Name= ox.Name,
Employee.Sal = ox.sal

from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID

# re: How To: Insert and Update with OpenXML 5/31/2007 7:32 PM Richard

Thanks, this was really useful.

It made some things clear when other sources had failed me.

# svyzr oeakmz 10/6/2007 8:59 AM lhkjcyep@mail.com

hgea pxynahu vowdaxt uvwhk xrijmlh lqfpnu djwetkp

# re:How To: insert and update with OpenXML 10/17/2007 2:17 AM Amit

Thanks it's great

# havni wbhvayx 10/24/2007 8:00 PM tpohuxdf@mail.com

epmsbrg hocp cuyktijl roikleujz qwjsclg sdvtb cgmij

# re: How To: Insert and Update with OpenXML 11/26/2007 6:40 AM Jim Nantz

I am trying to execute this as a dynamic query, but I can't seem to get it to work. Below is the code. If I print the print the output of @SQL and then run it, it works fine. But if I use EXEC sp_executesql @SQL, I get errors at "@handle" and "with". Does anyone have any suggestions? Thanks!

SET @SQL = 'UPDATE ' + @TableName + '
SET ' + CONVERT(NVARCHAR(4000), @ColumnName) + '
FROM OpenXml(' + CONVERT(NVARCHAR(10), @handle) + ', ''row'') with (' + CONVERT(NVARCHAR(4000), @ColumnNameTypeLength) + ') as ox
WHERE ' + @TableName + '.' + @UniqueIdentifier + ' = ox.' + @UniqueIdentifier
EXEC sp_executesql @SQL

# re: How To: Insert and Update with OpenXML 12/19/2007 12:00 PM Traviesus

Very useful, many thanks!
:)

# re: How To: Insert and Update with OpenXML 1/9/2008 3:16 PM Austin

I am trying to do an update using the given method but when I do I alway get a bind failure when I try to execute the update.

The multi-part identifier "tblGroup.GroupId" could not be bound.

SQL:
UPDATE tblGroup
set [tblGroup].[Type] = convert(int, [group].[Type]), [tblGroup].[Name] = [group].[Name], tblGroup.HeaderText = Group.HeaderText
SELECT * FROM OPENXML(@hdoc, 's/g', 1)
WITH (GroupId int, Type int, Name nvarchar(255), HeaderText nvarchar(255)) group
where tblGroup.GroupId = group.GroupId

All the datatypes in the db are the same as they are in the WITH clause. Is there something wrong with 'group' that its not there or are the datatypes different?

Anyone have any ideas?

Thanks in advance

# re: How To: Insert and Update with OpenXML 1/16/2008 3:02 AM Vinoth

It's really very helpful, Thx for such a great post

# re: How To: Insert and Update with OpenXML 7/17/2008 12:42 AM ZAK know as iloveuzak

dynamic query for INSERT using OpenXML

CREATE PROCEDURE dbo.xmlRowInsertUpdate
(
@xmlString NTEXT,
@tableName VARCHAR(100),
@isInsertRow INT
)
AS
BEGIN
DECLARE @docHandle INT
EXEC SP_XML_PREPAREDOCUMENT @docHandle OUTPUT, @xmlString
IF @isInsertRow = 1
BEGIN
INSERT INTO @tableName
SELECT * FROM OPENXML(@docHandle, '/' + @tableName)
WITH @tableName
EXEC SP_XML_REMOVEDOCUMENT @docHandle
END
ELSE
BEGIN
/* For Update*/
END
END

Thanks & Regards,
Zahed

# re: How To: Insert and Update with OpenXML 7/17/2008 1:19 AM ZAK know as iloveuzak

You have to use this SP for dynamic table name used on SP.... for OpenXML

this one is specially used to reduce the no.of Stored Procedures on to the Database for Insert.. one Single SP can be used accrose the application of inserting records,

[Code]

CREATE PROCEDURE dbo.xmlRowInsertUpdate
(
@xmlString NTEXT,
@tableName VARCHAR(100),
@isInsertRow INT
)
AS
BEGIN
DECLARE @docHandle INT
DECLARE @strSQL NVARCHAR(500)

EXEC SP_XML_PREPAREDOCUMENT @docHandle OUTPUT, @xmlString
IF @isInsertRow = 1
BEGIN
/* Preparing SQL to execute with OpenXML */
SET @strSQL = 'INSERT INTO ' + @tableName + ' SELECT * FROM OPENXML(@docHandle, /' + @tableName + ') WITH ' + @tableName

EXECUTE SP_EXECUTESQL @strSQL
EXEC SP_XML_REMOVEDOCUMENT @docHandle
END
END

[/CODE]

Thanks & Regards,
Zahed

# re: How To: Insert and Update with OpenXML 7/28/2008 5:41 AM ZAK know as iloveuzak

Here we go.. working copy of SP ;)

SP Creation Script:
*******************
CREATE PROCEDURE [dbo].[xmlRowInsertUpdate]
@xmlString NTEXT
AS
BEGIN
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlString
INSERT INTO ZAKTesting
SELECT * FROM OPENXML(@hDoc, '/ROOT/ZAKTesting',2) WITH ZAKTesting
EXEC sp_xml_removedocument @hDoc
END

Executing:
**********

EXEC dbo.xmlRowInsertUpdate '<ROOT><ZAKTesting><Name>Testing1</Name><Age>26</Age></ZAKTesting><ZAKTesting><Name>Testing2</Name><Age>27</Age></ZAKTesting><ZAKTesting><Name>Testing3</Name><Age>28</Age></ZAKTesting><ZAKTesting><Name>Testing4</Name><Age>29</Age></ZAKTesting><ZAKTesting><Name>Testing5</Name><Age>30</Age></ZAKTesting></ROOT>'

# re: How To: Insert and Update with OpenXML 8/27/2008 7:17 AM biliboi

This is a general procedure for insert:

ALTER PROCEDURE [dbo].[XmlRowInsert](
@tableName varchar(50),
@xmlString nvarchar(max)
)
AS
BEGIN
DECLARE @docHandle int
DECLARE @strSQL NVARCHAR(500)
DECLARE @paramDefinition nvarchar(500);
DECLARE @rowID int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlString
SET @strSQL = ''
SET @strSQL = @strSQL +
N'INSERT ' + @tableName +
' SELECT * FROM OPENXML(' +
'@docHandleParam, ''/ROOT/row'', 1) WITH ' + @tableName + ' '
SET @strSQL = @strSQL +
N'SELECT @rowIDOut = SCOPE_IDENTITY()'

SET @paramDefinition = N'@docHandleParam int, @rowIDOut int OUTPUT'
EXEC sp_executesql @strSQL, @paramDefinition, @docHandleParam = @docHandle, @rowIDOut = @rowID OUTPUT
EXEC sp_xml_removedocument @docHandle
SELECT @rowID
END

And here, one for update (manually):

ALTER PROCEDURE [dbo].[XmlRowUpdate](
@tableName varchar(50),
@idFieldName varchar(50),
@xmlString nvarchar(max)
)
AS
BEGIN
DECLARE @docHandle int
DECLARE @strSQL NVARCHAR(500)
DECLARE @paramDefinition nvarchar(500);
DECLARE @Columns TABLE (ColumnName VARCHAR(50), ColumnValue VARCHAR(500))
DECLARE @tempXmlString xml

SET @tempXmlString = CONVERT(xml, SUBSTRING(@xmlString, 7, LEN(@xmlString) - 13))
INSERT INTO @Columns (ColumnName, ColumnValue)
SELECT att.value('local-name(.)', 'VARCHAR(50)'), att.value('.', 'VARCHAR(500)')
FROM @tempXmlString.nodes('/*/@*') AS T(att)

DECLARE @ColumnName VARCHAR(50)
DECLARE @ColumnValue VARCHAR(500)
DECLARE @IDColumnValue VARCHAR(50)

SET @strSQL = 'UPDATE ' + @tableName + ' SET ';
DECLARE c1 CURSOR FOR SELECT ColumnName, ColumnValue FROM @Columns
OPEN c1
FETCH NEXT FROM c1 INTO @ColumnName, @ColumnValue
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnName <> @idFieldName
BEG

# re: How To: Insert and Update with OpenXML 8/27/2008 7:20 AM biliboi

I forgot to tell the sample:

<ROOT><row ID="5" name="biliboi" birth="08/27/2008" age="10"/></ROOT>

# re: How To: Insert and Update with OpenXML 8/27/2008 7:24 AM biliboi

Continue code for updating:

WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnName <> @idFieldName
BEGIN
SET @strSQL = @strSQL + @ColumnName + ' = ''' + @ColumnValue + ''','
END
ELSE
BEGIN
SET @IDColumnValue = @ColumnValue
END
FETCH NEXT FROM c1 INTO @ColumnName, @ColumnValue
END
CLOSE c1
DEALLOCATE c1

SET @strSQL = LEFT(@strSQL, LEN(@strSQL) - 1)
SET @strSQL = @strSQL + ' WHERE ' + @idFieldName + ' = ' + @IDColumnValue

EXEC sp_executesql @strSQL
END

# re: How To: Insert and Update with OpenXML 9/15/2008 8:17 PM kishore

good

# re: How To: Insert and Update with OpenXML 9/19/2008 2:41 AM sumesh

how can update with out cursor

any one pls help
thanks in advance

# re: How To: Insert and Update with OpenXML 10/14/2008 9:22 PM wat

i want to know how it work, i meant where we must put that code,either after connection open or before. please hlp...thanks

# sammi 2/3/2009 3:25 PM sammi

84gTZx djG39Bsk4chHy2M0xpk2Fv

# sammi 2/3/2009 3:26 PM sammi

84gTZx djG39Bsk4chHy2M0xpk2Fv

# sammi 2/3/2009 3:26 PM sammi

84gTZx djG39Bsk4chHy2M0xpk2Fv

# sammy 4/5/2009 2:43 PM sammy

Gi4Q00 vkoo7wvY5Xkfak7bf1Th

# sammy 4/5/2009 2:44 PM sammy

Gi4Q00 vkoo7wvY5Xkfak7bf1Th

# sammy 4/5/2009 2:45 PM sammy

Gi4Q00 vkoo7wvY5Xkfak7bf1Th

# sammy 4/5/2009 2:46 PM sammy

Gi4Q00 vkoo7wvY5Xkfak7bf1Th

Title
Name
Url
Comments
Remember Me?

FAQ #100

last updated:
10/28/2003

Did the information in this faq help answer your question?

Yes
No
Did Not Apply

Survey Results: 45

Yes

N/A


© 2001, 2002, 2003 sqlxml.org