Multi row insert using XMLDocPointer

This is my daily journal. I submit these things just for my future use or easy reference. That is why it is not explanatory article. If somebody can get benefit from it then it will be my pleasure.

–spName ‘<RECORD><Campaign XMLAttribute1=”4” XMLAttribute2=”sumeraas” XMLAttribute3=”TRUE” /></RECORD>’
CREATE PROCEDURE [spName]
(
@strXML text
)
AS

DECLARE @XMLDocPointer INT

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML

BEGIN TRANSACTION
—— Any DML starts from here ———-
BEGIN
INSERT INTO Tablename
(
Field1,
Field2,
Field3
)
SELECT XMLAttribute1, XMLAttribute2, XMLAttribute3
FROM OPENXML(@XMLDocPointer,’/RECORD/Campaign’)
WITH ( XMLAttribute1 VARCHAR(100), XMLAttribute2 VARCHAR(100), XMLAttribute3 VARCHAR(100))
END
COMMIT

EXEC sp_xml_removedocument @XMLDocPointer

EXEC sp_asw_DataImportCampaign

Advertisements

~ by UTS on April 21, 2009.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: