Bulk insert into sql database using xml file within stored proc not working
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]
Here is the code for the stored proc:
CREATE PROC AbsorbentOrderBulkInsertXML @items nText
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
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>
Hazardous Waste Management Services
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
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
2- Format your XML to look like
FROM OPENXML (@hDoc, '/absorbentitems',2)
No change required for the OPENXML function call it remains 1)
<item dpc="501" absorbentcode="CL1022" quantity="6"></item></absorbentitems>
Hope this works for you or anyone else
Mohamed M Faramawi
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! )
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
By mycwcgr in forum Database
Last Post: 09-25-2005, 04:40 AM
By mycwcgr in forum Database
Last Post: 09-03-2005, 10:03 PM
Last Post: 08-23-2001, 04:47 PM
Last Post: 01-13-2001, 02:15 PM
By Sai Anjesh in forum XML
Last Post: 07-13-2000, 03:43 AM
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center