Using OPENXML in stored Procedure

meeram395

I have a set of records which I want to insert to the table at one stretch. For this, I am using OpenXML keyword in Sql Server 2005. Following is my stored procedure:

ALTER procedure [dbo].[usp_InsertManyRows]

@XMLDOC xml

AS
declare @xml_hndl int

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

Insert Into outputfiles
(

        Processid

        )

Select

        Processid

From
OPENXML(@xml_hndl, '/Process/ProcessId')
With
(

                    Processid int '/Process/ProcessId'

                    )

The table 'OuputFiles' contains column ProcessId, OutputFileName and ContributionFileName columns. When I tried to test the sp as below, it was inserting the same record multiple times. I am not sure why it is:

exec usp_InsertManyRows '<Process><ProcessId>8712</ProcessId><ProcessId>14444</ProcessId></Process>'

It is only inserting 8712 two times, not at all 14444. I am using this for the first time. Please help me how to resolve this, as well as inserting the values for multiple columns like OutputFileName and ContributionFileName columns. Thanks in advance

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Goutam Patra

You can try like the following For SQL

Select
Processid
From
OPENXML(@xml_hndl, 'root/Process')
With
(
ProcessId int

)

Use XML as

'<root><Process ProcessId="8712"></Process> <Process ProcessId="1424"></Process></root>'

Look http://msdn.microsoft.com/en-us/library/aa276847%28SQL.80%29.aspx[^]