|
How To: Insert and Update with OpenXML (68619 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
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
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
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
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
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
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
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 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
# 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=”<?xml version="1.0" encoding="UTF-8"?> <email emailAddr= " xxx.yyy@verizon.com " ><FirstName> < xxx/FirstName> < LastName> yyy< /LastName> < /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
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=”<?xml version="1.0" encoding="UTF-8"?> <email emailAddr= " xxx.yyy@verizon.com " ><FirstName> < xxx/FirstName> < LastName> yyy< /LastName> < /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/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.
# re:How To: insert and update with OpenXML
10/17/2007 2:17 AM
Amit
Thanks it's great
# 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
# re: How To: Insert and Update with OpenXML
4/4/2010 7:43 PM
cuti88
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
What will happen if i change above code snippet:
From:
<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>
TO:
<mydata>
<test xmlID="1" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>
# re: How To: Insert and Update with OpenXML
6/29/2010 7:21 AM
Mohsin
Very Nice Solution..... it solves my problem
# re: How To: Insert and Update with OpenXML
7/11/2010 11:50 PM
Rey
in "WITH", what is "xmlID" ??? the SQL PK or the xml parameter ?
it'd better use DIFFERENT names.
# re: How To: Insert and Update with OpenXML
10/1/2010 3:29 AM
Mahalakshmi
how it is implemented with vb.net
# re: How To: Insert and Update with OpenXML
12/21/2010 8:53 AM
Mohammed
how to Create XML Update SP
# re: How To: Insert and Update with OpenXML
8/29/2011 12:45 AM
Anil Gupta
Amazing code... i'm looking a similar type of code in www.
Thanks!!
29HB5R Im obliged for the post. Great.
dWj7MA Thanks for the blog. Really Great.
# eANj6o Thanks-a-mundo for the blog post. Want more.
9/19/2012 7:58 PM
cheap bookmarking service
eANj6o Thanks-a-mundo for the blog post. Want more.
# k5MSBv A big thank you for your post.Really thank you! Keep writing.
9/20/2012 12:03 PM
bookmarking submission
k5MSBv A big thank you for your post.Really thank you! Keep writing.
lO52Fj Very good blog.Really looking forward to read more.
oUpYtg Im thankful for the blog article.Thanks Again. Want more.
iOCo0o Thanks a lot for the article.Really looking forward to read more. Fantastic.
xEcSG4 Fantastic article post.Thanks Again. Really Cool.
Dc6i64 I really like and appreciate your blog article.Really thank you! Really Cool.
90rDNK Looking forward to reading more. Great article post.Thanks Again. Much obliged.
90rDNK Looking forward to reading more. Great article post.Thanks Again. Much obliged.
# SfMMRqJELpdAZoOlK
4/24/2013 4:19 AM
http://crork.com/
oFYxv5 Really informative blog post. Cool.
hLrJYu Very informative article. Want more.
hLrJYu Very informative article. Want more.
Great article.Much thanks again. Great.
Thank you for your blog post.Really looking forward to read more. Fantastic.
A big thank you for your blog. Fantastic.
I truly appreciate this article post.Thanks Again. Awesome.
I really liked your blog.Really looking forward to read more. Will read on...
Very good blog article. Keep writing.
I value the blog article. Fantastic.
Very good blog article.Really looking forward to read more. Fantastic.
Really informative post.Thanks Again. Really Great.
Im grateful for the blog post.Really looking forward to read more. Really Great.
I cannot thank you enough for the post.Thanks Again. Fantastic.
Wow, great article.Really thank you! Cool.
I really like and appreciate your blog.Really looking forward to read more.
Looking forward to reading more. Great blog article.Much thanks again. Want more.
Thanks-a-mundo for the post.Thanks Again. Really Cool.
wow, awesome article post. Fantastic.
I cannot thank you enough for the blog post.Much thanks again. Keep writing.
I value the article post.Thanks Again. Cool.
Major thanks for the blog post.Thanks Again.
Major thankies for the article post.Really thank you! Keep writing.
Im obliged for the article.Thanks Again. Cool.
Thanks for sharing, this is a fantastic blog.Really looking forward to read more. Really Cool.
Very good article.Thanks Again. Will read on...
Im thankful for the blog article.Thanks Again. Really Great.
Appreciate you sharing, great blog.Really looking forward to read more. Cool.
wow, awesome blog post.Thanks Again. Keep writing.
I really liked your blog post.Really thank you! Awesome.
wow, awesome blog article. Much obliged.
Thanks for sharing, this is a fantastic blog.Much thanks again. Really Great.
I really liked your article.Much thanks again. Will read on...
Thanks so much for the post.Much thanks again. Awesome.
This is one awesome article.Much thanks again. Will read on...
wow, awesome blog post.Really thank you! Fantastic.
Fantastic article.Really thank you! Will read on...
I am so grateful for your blog article. Keep writing.
I truly appreciate this article post.Thanks Again. Great.
Very informative article.Really looking forward to read more. Really Great.
Awesome article.Really looking forward to read more. Awesome.
Hey, thanks for the blog post.Really thank you! Really Cool.
I value the article.Thanks Again. Fantastic.
Thanks a lot for the blog.Really thank you! Fantastic.
Enjoyed every bit of your article post. Will read on...
Hey, thanks for the article.Really looking forward to read more. Will read on...
I really liked your blog.Thanks Again. Will read on...
Very neat blog post.Really thank you! Cool.
Enjoyed every bit of your blog article. Really Great.
wow, awesome article.Really looking forward to read more. Much obliged.
Muchos Gracias for your blog article.Thanks Again. Will read on...
I value the post.Really looking forward to read more. Keep writing.
I loved your blog post.Really thank you! Awesome.
Enjoyed every bit of your blog article.Really looking forward to read more. Really Cool.
Very good blog.Really looking forward to read more.
A big thank you for your blog article.Thanks Again. Keep writing.
I value the blog.Thanks Again. Will read on...
Thanks for sharing, this is a fantastic article post.Much thanks again.
Really informative blog article.Much thanks again. Will read on...
Im grateful for the article.Thanks Again. Keep writing.
Major thanks for the article post.Really looking forward to read more. Really Great.
I truly appreciate this blog post.Really looking forward to read more. Great.
I cannot thank you enough for the blog post.Much thanks again. Want more.
A round of applause for your blog article.Really looking forward to read more. Really Great.
wow, awesome blog article.Thanks Again.
Enjoyed every bit of your post.Thanks Again. Cool.
I appreciate you sharing this article post.Really looking forward to read more. Really Great.
Looking forward to reading more. Great article.Really looking forward to read more. Cool.
I really enjoy the post.Really looking forward to read more. Really Great.
I truly appreciate this article post.Much thanks again.
Appreciate you sharing, great blog.Much thanks again. Awesome.
I think this is a real great article post.Really looking forward to read more. Great.
Im obliged for the blog post.Thanks Again.
I really enjoy the blog post.Thanks Again. Fantastic.
Great blog post. Fantastic.
Very good blog.Really thank you! Great.
Great, thanks for sharing this article.Much thanks again. Really Great.
ulq9v7 I really enjoy the post.
ulq9v7 I really enjoy the post.
Say, you got a nice article post. Keep writing.
ycEPGF A big thank you for your article post.Much thanks again. Much obliged.
ycEPGF A big thank you for your article post.Much thanks again. Much obliged.
Thank you ever so for you post. Will read on...
Very neat blog article.Really thank you! Great.
Thanks-a-mundo for the blog.Really looking forward to read more. Really Great.
A big thank you for your blog post.Really thank you!
Looking forward to reading more. Great blog article. Great.
I loved your post.Really looking forward to read more.
Say, you got a nice article.Much thanks again. Want more.
This is one awesome blog.Really thank you! Will read on...
Great, thanks for sharing this article post.Really looking forward to read more. Awesome.
Fantastic article post.Really thank you! Will read on...
A big thank you for your blog. Really Cool.
Im obliged for the article post.Really thank you! Great.
A big thank you for your blog.Much thanks again. Awesome.
This is one awesome post.Much thanks again. Awesome.
Thanks a lot for the article.Much thanks again. Fantastic.
I think this is a real great blog.Really thank you! Great.
Hey, thanks for the article post.Really thank you! Really Cool.
I loved your blog.Much thanks again. Great.
I loved your blog.Much thanks again. Great.
Thanks for sharing, this is a fantastic post. Will read on...
I am so grateful for your article. Really Great.
I am so grateful for your blog article.Thanks Again. Cool.
I value the article. Keep writing.
A round of applause for your blog post.Really thank you! Much obliged.
Appreciate you sharing, great blog. Much obliged.
I really liked your blog.Really looking forward to read more. Want more.
Say, you got a nice blog article.Really looking forward to read more. Much obliged.
Major thanks for the blog article.Much thanks again. Really Cool.
Enjoyed every bit of your article post. Want more.
I am so grateful for your blog post.Really thank you! Really Cool.
I truly appreciate this blog post. Will read on...
I cannot thank you enough for the blog article.Really looking forward to read more. Great.
wow, awesome article.Much thanks again. Want more.
Really informative post.Really thank you! Want more.
Very neat article post.Much thanks again. Awesome.
I think this is a real great blog article.Really looking forward to read more. Great.
Muchos Gracias for your article post.Really looking forward to read more.
A round of applause for your blog.Really thank you! Fantastic.
Thanks again for the blog post.Really looking forward to read more. Much obliged.
Thanks so much for the article post.Really looking forward to read more. Much obliged.
Looking forward to reading more. Great blog article.Really looking forward to read more. Awesome.
Say, you got a nice article. Really Great.
Say, you got a nice article. Really Great.
Really informative blog article.Really looking forward to read more. Really Cool.
Thanks for sharing, this is a fantastic blog article. Want more.
Really enjoyed this blog post.Much thanks again. Really Cool.
Major thanks for the blog article.Really thank you! Fantastic.
Very neat blog.Thanks Again. Great.
This is one awesome post.Thanks Again. Much obliged.
Thank you ever so for you post.Really thank you! Cool.
Thanks again for the post.Much thanks again. Really Cool.
Thanks-a-mundo for the post. Cool.
I think this is a real great blog.Thanks Again.
Thanks-a-mundo for the article post.Really looking forward to read more.
I value the blog.Thanks Again. Want more.
Great, thanks for sharing this blog post.Really looking forward to read more. Much obliged.
Very good post.Much thanks again.
Appreciate you sharing, great blog article.Really looking forward to read more. Cool.
I really liked your post.Thanks Again. Cool.
Say, you got a nice article.Much thanks again. Awesome.
Great, thanks for sharing this blog. Really Cool.
Wow, great blog.Thanks Again. Fantastic.
wow, awesome article post.Really looking forward to read more. Awesome.
Really enjoyed this blog. Great.
Thanks-a-mundo for the blog post.Much thanks again. Really Great.
I really enjoy the blog post.Much thanks again. Fantastic.
Im grateful for the blog. Keep writing.
Great post.Really looking forward to read more. Really Great.
I really liked your article post.Thanks Again. Awesome.
A round of applause for your article.Thanks Again. Want more.
Wow, great blog. Really Cool.
Thank you ever so for you blog article.Really thank you! Want more.
Fantastic post.Much thanks again. Will read on...
Im thankful for the article.Thanks Again. Really Cool.
Im thankful for the blog post. Awesome.
Looking forward to reading more. Great blog.Thanks Again. Keep writing.
Im grateful for the article post. Keep writing.
Major thanks for the article.Much thanks again. Fantastic.
Muchos Gracias for your article. Fantastic.
Enjoyed every bit of your article post. Great.
I loved your blog article.Really looking forward to read more.
I appreciate you sharing this blog article.Much thanks again. Cool.
I think this is a real great post.Really looking forward to read more. Really Cool.
I am so grateful for your article post.Thanks Again. Great.
I truly appreciate this blog. Really Great.
Very neat article post.Thanks Again. Fantastic.
Really informative article post.Much thanks again. Really Cool.
wow, awesome blog post.Thanks Again. Cool.
I really liked your post.Really thank you! Fantastic.
Fantastic article. Really Cool.
Really enjoyed this blog article. Want more.
I loved your article.Thanks Again. Much obliged.
I value the article post.Thanks Again. Much obliged.
Wow, great article post.Really thank you! Cool.
I truly appreciate this blog article.
Really informative blog post.Thanks Again. Really Cool.
Wow, great post.Really thank you! Much obliged.
Thanks-a-mundo for the blog article. Really Great.
Major thanks for the post.Much thanks again. Keep writing.
Muchos Gracias for your article post.Really looking forward to read more. Great.
I am so grateful for your blog post.Thanks Again. Much obliged.
Thanks for sharing, this is a fantastic blog article.Much thanks again. Really Great.
Enjoyed every bit of your post.Thanks Again. Cool.
Awesome blog.Really thank you! Much obliged.
Say, you got a nice post.Thanks Again. Really Cool.
Great, thanks for sharing this article.Much thanks again. Keep writing.
I am so grateful for your post.Much thanks again. Great.
Thank you ever so for you blog.Much thanks again. Will read on...
Hey, thanks for the article. Fantastic.
Thank you ever so for you blog. Will read on...
Really appreciate you sharing this article.Really thank you! Awesome.
Thanks-a-mundo for the post.Thanks Again. Really Cool.
Thanks for the blog post. Keep writing.
Fantastic article post.Thanks Again. Keep writing.
Major thanks for the blog post.Much thanks again. Cool.
Great, thanks for sharing this article.Really thank you! Want more.
I appreciate you sharing this blog post.Thanks Again. Cool.
Appreciate you sharing, great article.Much thanks again. Will read on...
Appreciate you sharing, great post.Really looking forward to read more. Fantastic.
Looking forward to reading more. Great blog. Much obliged.
Great article.Really looking forward to read more. Keep writing.
Major thankies for the article. Much obliged.
Very good article.Much thanks again. Want more.
I value the post.Really thank you! Much obliged.
Major thanks for the blog.Thanks Again. Cool.
Muchos Gracias for your post.Thanks Again. Really Cool.
wow, awesome article.Really thank you! Really Cool.
I really like and appreciate your blog. Will read on...
I really enjoy the article post.Much thanks again. Cool.
I really enjoy the article post.Much thanks again. Cool.
I value the post.Really thank you! Really Cool.
wow, awesome article.Really thank you! Will read on...
Thanks so much for the blog article.Really thank you! Want more.
Muchos Gracias for your blog article.Really looking forward to read more. Awesome.
A round of applause for your post.Thanks Again. Really Cool.
Say, you got a nice blog post.Really thank you! Fantastic.
Very good article post. Cool.
Fantastic blog post.
Great, thanks for sharing this blog post.Really thank you! Want more.
Really informative article post. Really Great.
Really appreciate you sharing this blog post.Really thank you! Really Great.
Wow, great blog article.Really thank you! Great.
Enjoyed every bit of your post. Fantastic.
Very informative post. Much obliged.
Major thankies for the article post.Much thanks again. Awesome.
Thank you ever so for you blog article.Really thank you! Keep writing.
Thanks for the article post.Much thanks again. Fantastic.
I loved your article post.Thanks Again. Awesome.
Muchos Gracias for your post. Really Cool.
I really liked your article.Really thank you! Cool.
Thank you for your article.Really thank you! Much obliged.
Very good article post.Really looking forward to read more. Really Great.
Major thankies for the blog post.Really thank you! Really Great.
Thanks-a-mundo for the blog post.Thanks Again. Fantastic.
Very informative blog post.Really looking forward to read more. Really Cool.
Really appreciate you sharing this article post.Thanks Again. Really Great.
A big thank you for your article. Fantastic.
I really liked your blog article.Really thank you! Great.
I really liked your post.Really thank you! Really Cool.
Very good blog post.Thanks Again. Great.
Really enjoyed this post.Really looking forward to read more. Much obliged.
Really enjoyed this post.Really looking forward to read more. Much obliged.
A round of applause for your article post.Much thanks again. Much obliged.
Im obliged for the blog article. Awesome.
Say, you got a nice post.Really looking forward to read more. Keep writing.
EiUuGt Thanks for sharing, this is a fantastic blog.Much thanks again.
I truly appreciate this post.Thanks Again. Awesome.
Thanks a lot for the blog post.Really looking forward to read more. Want more.
Major thankies for the blog.Thanks Again. Cool.
Thanks-a-mundo for the blog article.Thanks Again. Cool.
Very neat blog post.Thanks Again. Fantastic.
I am so grateful for your article post.Really looking forward to read more. Keep writing.
I think this is a real great blog.Much thanks again. Want more.
Great, thanks for sharing this article post.Really looking forward to read more. Really Great.
gLVAYq I value the article.Really looking forward to read more.
Appreciate you sharing, great post.Really looking forward to read more. Will read on...
Really appreciate you sharing this article post.Really thank you! Really Cool.
A round of applause for your blog.Thanks Again. Great.
I appreciate you sharing this blog post.Really thank you! Keep writing.
Enjoyed every bit of your post.Thanks Again. Much obliged.
Im grateful for the article.Really looking forward to read more. Really Great.
This is one awesome article.Really looking forward to read more. Cool.
Enjoyed every bit of your article post.Really looking forward to read more. Much obliged.
I am so grateful for your article.Really thank you!
I value the post.Really thank you! Really Great.
Thanks-a-mundo for the article.Much thanks again. Fantastic.
Thanks-a-mundo for the article.Much thanks again. Fantastic.
Great blog post.Really thank you! Really Great.
I am so grateful for your article.Thanks Again. Want more.
Really appreciate you sharing this article.Much thanks again. Will read on...
Say, you got a nice post.Much thanks again. Awesome.
# NyQxJpWWjvnDDBtDREY
5/15/2013 3:56 AM
http://dubturbo.pw
wow, awesome blog article.Much thanks again. Fantastic.
Say, you got a nice post.Really looking forward to read more. Fantastic.
I value the blog article.Really looking forward to read more. Keep writing.
A round of applause for your article post.Thanks Again. Cool.
Im thankful for the article.Thanks Again. Much obliged.
Major thanks for the article post.Much thanks again. Want more.
Im grateful for the blog article.Really looking forward to read more. Great.
I cannot thank you enough for the article.Really looking forward to read more. Keep writing.
Awesome blog.Really looking forward to read more. Want more.
I am so grateful for your blog.Much thanks again. Keep writing.
Very informative blog article.Thanks Again. Really Great.
I think this is a real great article post. Awesome.
Appreciate you sharing, great blog post.Really thank you! Great.
Wow, great article. Keep writing.
Wow, great blog.Really looking forward to read more.
Appreciate you sharing, great blog article.Really thank you! Cool.
This is one awesome article post. Really Cool.
I value the article post.Thanks Again. Fantastic.
Im obliged for the article.Thanks Again. Awesome.
Thanks-a-mundo for the blog article.Much thanks again. Much obliged.
Thank you ever so for you post.Really thank you! Want more.
This is one awesome blog.Really looking forward to read more. Keep writing.
I am so grateful for your blog. Want more.
Im thankful for the article.Much thanks again.
A round of applause for your blog article.Really looking forward to read more. Awesome.
This is one awesome blog article.Thanks Again.
Very informative blog post.Thanks Again. Want more.
Very informative blog post.Thanks Again. Want more.
Really appreciate you sharing this post. Much obliged.
I really liked your blog article.Much thanks again. Much obliged.
Really informative blog post.Thanks Again. Awesome.
Major thankies for the article post.Much thanks again. Will read on...
Looking forward to reading more. Great blog.Really thank you! Keep writing.
Appreciate you sharing, great article post.Really looking forward to read more. Will read on...
I loved your article post.Thanks Again. Awesome.
Im obliged for the blog. Really Great.
I really like and appreciate your post.Really looking forward to read more. Great.
Major thankies for the blog post.Much thanks again. Keep writing.
Thanks for sharing, this is a fantastic blog. Will read on...
I really enjoy the blog post.Thanks Again. Really Cool.
Thank you ever so for you article.Really looking forward to read more. Keep writing.
Really informative article.Really looking forward to read more.
Very informative article.Really looking forward to read more. Awesome.
Really informative blog post.Really looking forward to read more. Really Cool.
I truly appreciate this article. Cool.
Very informative article.Really looking forward to read more. Want more.
Thank you for your blog post.Really looking forward to read more. Will read on...
I am so grateful for your article post.Really thank you! Awesome.
I really enjoy the post.Thanks Again. Awesome.
Thank you for your post.Really thank you! Much obliged.
Wow, great blog.Much thanks again.
I appreciate you sharing this blog article.Thanks Again. Really Cool.
Great blog post.Much thanks again. Awesome.
Thanks again for the article post.Really looking forward to read more. Will read on...
Im obliged for the blog post. Will read on...
I really enjoy the post. Really Great.
Thank you ever so for you blog post.Thanks Again. Fantastic.
Looking forward to reading more. Great blog.Really thank you! Great.
wow, awesome blog post.Really looking forward to read more. Awesome.
Very good post.Really thank you! Cool.
Very good blog.Really thank you! Want more.
A big thank you for your post.Thanks Again. Really Great.
This is one awesome blog post. Great.
Very informative article post.Thanks Again. Really Great.
Thanks-a-mundo for the blog article.Really thank you! Will read on...
Thanks a lot for the article.Thanks Again. Great.
Thank you ever so for you blog article.Thanks Again. Will read on...
I value the blog post.Much thanks again. Really Cool.
Im grateful for the blog.Really looking forward to read more. Cool.
I loved your post.Thanks Again. Awesome.
Enjoyed every bit of your blog article.Really looking forward to read more. Great.
Appreciate you sharing, great article.Much thanks again. Much obliged.
A854b2 Major thankies for the blog post.Really thank you! Really Cool.
I really enjoy the blog post. Awesome.
# QHpdqxKMETtXITkbZSc
5/18/2013 8:22 AM
http://crork.com/
Q4jNpe Very informative blog article. Really Cool.
Thanks for the post. Great.
A round of applause for your blog post. Awesome.
Thanks so much for the blog.Really thank you! Really Great.
I cannot thank you enough for the article post.Really thank you! Awesome.
I cannot thank you enough for the article post.Really thank you! Awesome.
Great blog.Really looking forward to read more. Cool.
Major thankies for the blog.Thanks Again. Really Cool.
Major thankies for the blog post. Want more.
Looking forward to reading more. Great article.Really looking forward to read more. Much obliged.
A round of applause for your blog article.Much thanks again. Cool.
Major thanks for the blog article.Much thanks again. Cool.
Thanks a lot for the article. Keep writing.
Great, thanks for sharing this post. Fantastic.
I appreciate you sharing this post.Thanks Again. Really Cool.
Hey, thanks for the blog.Really thank you! Fantastic.
Thanks a lot for the blog.Thanks Again. Will read on...
Appreciate you sharing, great blog post.Much thanks again. Really Great.
Very good post.Much thanks again. Great.
A big thank you for your blog.Really looking forward to read more. Keep writing.
I loved your blog post.Really thank you! Awesome.
Major thanks for the blog post.Really thank you! Fantastic.
I truly appreciate this article.Really looking forward to read more. Great.
I really like and appreciate your post.Much thanks again. Awesome.
I appreciate you sharing this blog article. Want more.
I think this is a real great blog article.Thanks Again. Will read on...
Hey, thanks for the blog.Really thank you! Want more.
Hey, thanks for the blog. Fantastic.
wow, awesome article post. Great.
I really enjoy the blog.Really thank you! Fantastic.
|