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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Feb 2006
    Posts
    19

    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

  2. #2
    Join Date
    Sep 2008
    Location
    Egypt
    Posts
    6
    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

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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

Similar Threads

  1. Replies: 0
    Last Post: 09-25-2005, 04:40 AM
  2. Replies: 1
    Last Post: 09-03-2005, 10:03 PM
  3. XML from Mainframe flat file
    By Gee in forum XML
    Replies: 11
    Last Post: 08-23-2001, 04:47 PM
  4. Replies: 7
    Last Post: 01-13-2001, 02:15 PM
  5. XML docs stored in Database
    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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center