Bulk load xml file to sql server 2005 table

Read this

http://www.fryan0911.com/2009/06/vbnet-how-to-update-record-on-sql.html

Or revert back if you need more help
--Rahul D.

Ok fine . I have Vb.net Code . I will modify it as per your query and give you ASAP.

Ok Follow this step :

I would suggest try this in separate console application first and learn how it works

1) Create below procedure in database

USE [TST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SaveTrnBal]
@filepath AS NVARCHAR(255)
AS
BEGIN

DECLARE @CD TABLE (XMLData XML);
DECLARE @sql AS NVARCHAR(MAX)

SET @sql = 'SELECT * FROM OPENROWSET(BULK ''' + @filepath + ''', Single_Blob) as tt '

INSERT INTO @CD
Exec (@sql)

INSERT INTO TST.dbo.TrnBal (LR_no, LR_Date, Bkg_Stn, FrmShort, Dst, ToShort, Consignor_Name, Consignee_Name, Pvt_Mark, Discription, Article,
Weight,Value,Pmt_Mode,DoorDel,LR_Remark,Freight,BC,Handling,DDel,LC,Others,S_Tax,Amount,Initial,Report_No,Rep_Date)
SELECT LR_No = x.data.value('LR_No[1]', 'int'),
LR_Date = x.data.value('LR_Date[1]', 'Varchar(50)'),
Bkg_Stn = x.data.value('Bkg_Stn[1]', 'Varchar(30)'),
FrmShort = x.data.value('FrmShort[1]', 'Varchar(5)'),
Dst = x.data.value('Dst[1]', 'Varchar(30)'),
ToShort = x.data.value('ToShort[1]', 'Varchar(5)'),
Consignor_Name = x.data.value('Consignor_Name[1]', 'Varchar(30)'),
Consignee_Name = x.data.value('Consignee_Name[1]', 'Varchar(30)'),
Pvt_Mark = x.data.value('Pvt_Mark[1]', 'Varchar(20)'),
Discription = x.data.value('Discription[1]', 'Varchar(20)'),
Article = x.data.value('Article[1]', 'decimal(18,0)'),
Weight = x.data.value('Weight[1]', 'decimal(18,0)'),
Value= x.data.value('Value[1]', 'decimal(18,0)'),
Pmt_Mode = x.data.value('Pmt_Mode[1]', 'Varchar(5)'),
DoorDel = x.data.value('DoorDel[1]', 'Varchar(5)'),
LR_Remark = x.data.value('LR_Remark[1]', 'Varchar(50)'),
Freight = x.data.value('Freight[1]', 'decimal(18,0)'),
BC = x.data.value('BC[1]', 'decimal(18,0)'),
Handling = x.data.value('Handling[1]', 'decimal(18,0)'),
DDel = x.data.value('DDel[1]', 'decimal(18,0)'),
LC = x.data.value('LC[1]', 'decimal(18,0)'),
Others = x.data.value('Others[1]', 'decimal(18,0)'),
S_Tax = x.data.value('S_Tax[1]', 'decimal(18,0)'),
Amount = x.data.value('Amount[1]', 'decimal(18,0)'),
Initial = x.data.value('Initial[1]', 'Varchar(15)'),
Report_No = x.data.value('Report_No[1]', 'int'),
Rep_Date = x.data.value('Rep_Date[1]', 'smalldatetime')
FROM @CD t
CROSS APPLY t.XMLData.nodes('NewDataSet/LRTest') x(data);

END


2) Create one application and copy-paste below code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CP_322265_Bulk_load_xml_file_to_sql_server_2005_table
{
class Program
{
static void Main(string[] args)
{

SqlConnection conn = null;
SqlDataReader rdr = null;

try

{



// create and open a connection object
conn = new
SqlConnection("Server=MDT765;DataBase=TST;User Id=TestUser;Password=TestUser@123;Integrated Security=SSPI");
conn.Open();

// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"SaveTrnBal", conn);

// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(
new SqlParameter("@filepath", @"D:\fxmlf.xml"));

// execute the command
rdr = cmd.ExecuteReader();

// iterate through results, printing each to console
while (rdr.Read())
{
Console.WriteLine("Sucess");
}
}
finally
{
if (conn != null)
{
conn.Close();
}

Hope this helps , If yes then accept and vote the answer otherwise revert back with your queries
--Rahul D.

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim lreturn As Boolean = True

Try
con.ConnectionString = "Server=MDT765;DataBase=TST;User Id=TestUser;Password=TestUser@123;Integrated Security=SSPI"
con.Open()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SaveTrnBal"
cmd.Parameters.Add("@filepath", SqlDbType.NVarChar)

cmd.Parameters("@filepath").Direction = ParameterDirection.Input
cmd.Parameters("@filepath").Value = "D:\fxmlf.xml"
cmd.ExecuteNonQuery()
Console.Write("Success")
Catch ex As Exception
lreturn = False
Console.Write("Error")
Finally
con.Close()
End Try

Hope this helps , If yes then accept and vote the answer otherwise revert back with your queries
--Rahul D.

Thanks for overwhelming response. I will get back to you.
Any way where you are working on ?

I would be happy to be a part of your project life cycle.

No prob