-
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]
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
-
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
Code:
<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
Code:
FROM OPENXML (@hDoc, '/absorbentitems',2)
2- Format your XML to look like
Code:
<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
-
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! )
Similar Threads
-
By mycwcgr in forum Database
Replies: 0
Last Post: 09-25-2005, 04:40 AM
-
By mycwcgr in forum Database
Replies: 1
Last Post: 09-03-2005, 10:03 PM
-
Replies: 11
Last Post: 08-23-2001, 04:47 PM
-
Replies: 7
Last Post: 01-13-2001, 02:15 PM
-
By Sai Anjesh in forum XML
Replies: 0
Last Post: 07-13-2000, 03:43 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|