Sunday 25 March 2012

insert from xml in sql stored procedure


CREATE PROCEDURE [dbo].[InsertSP]
@strXML varchar(1000)
AS
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
Insert into Months
Select * from OpenXml(@intPointer,'/months/month',2)
WITH ( nMonthNo int, cMonth varchar(15))

exec sp_xml_removedocument @intPointer
RETURN
GO

and call the procedure as follows,

exec insertsp '<months><month><nMonthNo>1</nMonthNo><cMonth>January</cMonth></month><month><nMonthNo>2</nMonthNo><cMonth>February</cMonth></month></months>'

No comments:

Post a Comment