Passing xml string parameter to SQL Server stored procedure

Question

I am passing an xml string to a stored procedure in SQL Server to insert 10,000 records into a table. In this case, I call this stored procedure. Want to check a SQL Server table with this xml string that I pass if the record exists, I don't want to insert it if it is a new record that only one record should insert. Give some solution. Thank you

ALTER procedure [dbo].[SP_CMSUSER1] (@xmlString ntext) as begin DECLARE @idoc INT DECLARE @data nvarchar(100) EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString INSERT INTO dbo.Seg_RecipientsTemp (ContactID,first_name,last_name,company,email,last_updated) SELECT ContactID, first_name, last_name, company, email, last_updated FROM OPENXML(@idoc, '/NewDataSet/ContactData', 6) WITH (ContactID int , first_name nvarchar(50), last_name nvarchar(50), company nvarchar(max), email nvarchar(100), last_updated datetime ) end 

My Xml:

  <NewDataSet> <Table> <ContactID>2</ContactID> <last_name>klklk</last_name> </Table> <Table> <ContactID>4</ContactID> <first_name>k</first_name> <last_name>kk</last_name> <company>k</company> </Table> <Table> <ContactID>6</ContactID> <first_name>naveen</first_name> <last_name /> <company>inno</company> </Table> <Table> <ContactID>7</ContactID> <first_name>sridar</first_name> <last_name /> <company>mahindara</company> </Table> <Table> <ContactID>1</ContactID> <first_name>terst</first_name> </Table> <Table> <ContactID>2</ContactID> <first_name /> <last_name>ask</last_name> <company /> </Table> </NewDataSet> 

Answer 1

Define your stored procedure to accept an XML type parameter (no longer use ntext !), Which is deprecated). And do not use the sp_ prefix for your stored procedures - this is a reserved prefix for internal Microsoft use and causes performance degradation - use something else! (or not use any prefix at all)

  ALTER procedure [dbo].InsertCmsUser @xmlString XML AS ...... 

Try this (using the native XQuery method in SQL Server 2005 and newer, instead of the rather dirty OPENXML interface ....):

 ;WITH CTE AS ( SELECT ContactID = XTbl.value('(ContactID)[1]', 'int'), FirstName = XTbl.value('(first_name)[1]', 'varchar(50)'), LastName = XTbl.value('(last_name)[1]', 'varchar(50)'), Company = XTbl.value('(company)[1]', 'varchar(50)') FROM @input.nodes('/NewDataSet/Table') AS XD(XTbl) ) INSERT INTO dbo.Seg_RecipientsTemp (ContactID, first_name, last_name, company, last_updated) SELECT ContactID, FirstName, LastName, Company, GETDATE() FROM CTE WHERE NOT EXISTS (SELECT * FROM dbo.Seg_RecipientsTemp WHERE ContactID = CTE.ContactID) 

I did not find the email attribute in your XML - not sure where you want to get this from ....

Update: ok, so you also have <last_updated> elements in real XML ....

 <last_updated>2012-09-12T22:59:10.813+05:30</last_updated> 

For me, it looks like DATETIMEOFFSET , since it has the addition of a time zone of +05:30 .

In this case, use this code instead:

 ;WITH CTE AS ( SELECT ContactID = XTbl.value('(ContactID)[1]', 'int'), FirstName = XTbl.value('(first_name)[1]', 'varchar(50)'), LastName = XTbl.value('(last_name)[1]', 'varchar(50)'), Company = XTbl.value('(company)[1]', 'varchar(50)'), LastUpdated = XTbl.value('(last_updated)[1]', 'datetimeoffset') FROM @input.nodes('/NewDataSet/Table') AS XD(XTbl) ) INSERT INTO dbo.Seg_RecipientsTemp (ContactID, first_name, last_name, company, last_updated) SELECT ContactID, FirstName, LastName, Company, LastUpdated FROM CTE WHERE NOT EXISTS (SELECT * FROM dbo.Seg_RecipientsTemp WHERE ContactID = CTE.ContactID)