ansaurus

Question

loading xml data into database using datatable

Answer 1

A:

Please post the code you're using to load the XML file into your DataTable.

Rip Rowan 2009-10-16 06:37:21

Yes, sir thanks for response. I have just posted the source code.

pravakar 2009-10-16 06:46:56

Answer 2

A:

You can try the following storedproc where you can send the xml content of the xml file and insert into table. visit this link

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE JobsBulkInsertXML @items xml

    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

Himadri 2009-10-16 07:11:56

I would advise **NOT** to use NTEXT (or TEXT or IMAGE) anymore - they are deprecated. Use VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) instead.

marc_s 2009-10-16 07:43:23

you can replace @items nText with @items xml

Himadri 2009-10-16 09:42:33

Answer 3

A:

I don't think it's a problem with your SQL code - but you need to be careful in your vb.net code when dealing with datarows that could contain empty fields:

Trim(Replace(dtRow.Item("DateExpire"), "'", "''"))
Trim(Replace(dtRow.Item("COUNTRY"), "'", "''"))

All these calls to Replace might fall flat on their nose if the dtRow.Item("COUNTRY") (or whichever you're using) is empty/NULL.

You need to be more careful applying those methods and check for DBNull.Value first!

Create a function something like this (in C# - should have no trouble converting that to VB.NET):

public string ReplaceString(DataRow row, string fieldname)
{
   // first make sure the value of the field in the row is not NULL !
   if(!row.IsNull(fieldname))
   {
       // only if NOT null, do the replace
       return row[fieldname].Replace("'", "''").Trim();
   }

   // if the field is DBNull, return an empty string
   return string.Empty;
}

and then use that in place of your explicit calls to Trim(Replace(...)).

Marc

marc_s 2009-10-16 07:59:29

ansaurus

tags:

views:

answers:

loading xml data into database using datatable

related questions