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
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL