Reputation: 391
I am passing an xml string to stored procedure in SQL Server for inserting 10000 records to my table. In this when I call this stored procedure. Want to check the SQL Server table with that xml string which I am passing, if the record exists I don't want to insert, if it is new record that record alone have to insert.Give some solution. Thanks.
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 is:
<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>
Upvotes: 3
Views: 55471
Reputation: 129
Simple try this
DECLARE @File varchar(MAX)='<?xml version="1.0" encoding="utf-8" ?><UpSellPremium><Premium><TransactionID>ICM_AUG_18_963589</TransactionID><UpSellPr>15009</UpSellPr></Premium><Premium><TransactionID>ICM_AUG_18_1330170</TransactionID><UpSellPr>18900</UpSellPr></Premium></UpSellPremium>'
DECLARE @idoc int
DECLARE @doc xml
SET @doc = @File
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
INTO #Temp_UpSellPr
FROM OpenXML(@idoc,'//Premium')
WITH
(
XMLTransactionID VARCHAR(50) 'TransactionID',
XMLUpSellPr FLOAT 'UpSellPr'
)
SELECT * FROM #Temp_UpSellPr
Upvotes: 0
Reputation: 166
I'm not allowed to comment yet, but hoping to help even though this isn't a real answer. This page offers a great detailed explanation:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/ (not affiliated)
The nodes() method The nodes() method can be a bit more slippery to understand than the other XML methods. To begin with, rather than returning XML or scalar values, the nodes() method returns what is essentially a table that includes one column. That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause. It also means that, when you call the nodes() method, you must assign a table alias and column alias to the rowset view returned by the method, as shown in the following syntax:
DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)
Upvotes: 0
Reputation: 755541
Define your stored procedure to take a parameter of type XML (don't use ntext
anymore! It's deprecated). And don't use the sp_
prefix for your stored procedures - it's a reserved prefix for internal use by Microsoft and causes performance degradation - use something else! (or don't use any prefix at all)
ALTER procedure [dbo].InsertCmsUser
@xmlString XML
AS
......
Try this (using the native XQuery methods in SQL Server 2005 and newer, instead of the rather messy 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 didn't find any email
attribute in your XML - not sure where you want to get that from ....
Update: ok, so you seem to also have <last_updated>
elements in your real XML ....
<last_updated>2012-09-12T22:59:10.813+05:30</last_updated>
This looks like a DATETIMEOFFSET
to me - since it has the +05:30
time zone addition.
In that 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)
Upvotes: 13