Bulk insert into sql database using xml file within stored proc not working

[clean]

Ive created a table with a few fields, created a stored proc that uses and xml file to to a bulk insert into the database. When i run the stored proc and pass the parameters below it does not insert the values into the database. Can someone please help for when i try to debug the stored proc it does not display anything so i am really stuck here.

I created the following table

CREATE TABLE [absorbentorder] (
[dpc] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[absorbentcode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[quantity] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Here is the code for the stored proc:

CREATE PROC AbsorbentOrderBulkInsertXML @items nText

AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT, @items

Insert Into absorbentorder
SELECT dpc, absorbentcode, quantity
FROM OPENXML (@hDoc, '/absorbentitems',1)
WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems

EXEC sp_xml_removedocument @hDoc


GO


Here is the parameter for the stored proc

<absorbentitems><item dpc="501"></item><item absorbentcode="CL1022"></item><item quantity="6"></item><item dpc="501"></item><item absorbentcode="CL1088"></item><item quantity="8"></item><item dpc="501"></item><item absorbentcode="CL1193"></item><item quantity="8"></item></absorbentitems>

thanks!

Alex Zaff
alex@cleanalliance.com
Hazardous Waste Management Services

[MohamedFaramawi]

Hi Alex,
I know this is a very old post, however in case some one else ran across it, i'm posting the solution.

The problem is with the format of your XML, you have 2 solutions
1- Format your XML to look like

<absorbentitems>
<item>
<dpc>501</dpc>
<absorbentcode>CL1022 </absorbentcode>
<quantity>6</quantity>
</item>
</absorbentitems>

did you notice how <item> element have the column as children and each column value is a node text not an attribute.
Then you will need to use "2" in the OPENXML function call

FROM OPENXML (@hDoc, '/absorbentitems',2)

2- Format your XML to look like

<absorbentitems>
<item dpc="501" absorbentcode="CL1022" quantity="6"></item></absorbentitems>

No change required for the OPENXML function call it remains 1)

Hope this works for you or anyone else

Regards
Mohamed M Faramawi
Software Engineer
HubSpot.com

[Hack]

Even though it is old, thanks for posting a solution.

Hopefully it will help someone with the same or similiar problem (and, they won't have to wait as long for an answer! )