In order to pass a large amount of data, in VB.NET we can use
Bulk Insert,
here's the article for more information on the sample too.
Bulk insert sample in vbs file, click
here.
If you need to create an XML from the Dataset, then u can click
here.
Here's a sample on reading XML format in stored procedure:
DECLARE @idoc int
DECLARE @doc varchar(1000);
Select @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="John 1"/>
<Customer CustomerID="2" ContactName="John 2"/>
<Customer CustomerID="1" ContactName="John 3"/>
<Customer CustomerID="2" ContactName="John 4"/>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @idoc
Please take note that this is not reading from the physical XML file, this can only be used for reading table field with the value of XML formatted text.
More info on feeding XML to Stored Procedure, click
here.
This is a sample of reading physical file from the stored procedure, but I have not tested it as I do not have this permission in my office development environment.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.I guess this action requires
sysadmin rights.
declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(4000)
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
-- Change the file path to the one that is passed to your stored procedure
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\test.txt', 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
exec sp_OAMethod @objFile, 'ReadLine', @strLine out
-- Here you got one line from the file
select @strLine
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys
