We can do bulk insert in sql server using xml data. Please go through the sample given below, which self explantory.
Sample:
CREATE PROCEDURE InsertEmployees
/* ----------------------------------------------------------
Parameters
In :
@strXML = contains employee list as XML
Example: '
'
---------------------------------------------------------- */
@strXML varchar(8000)
AS
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
INSERT into employee
SELECT EmpName, EmpSalary
FROM OpenXml(@intPointer,'/root/emp',2)
WITH EmpName varchar(20) '@EmpName' , EmpSalary varchar(20) '@EmpSalary')
exec sp_xml_removedocument @intPointer
-----------------------
EXEC InsertEmployees ''
sp_xml_preparedocument
Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on.
sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument.
OPENXML
OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
Sample:
OpenXml(@intPointer,'/root/emp',2)
WITH EmpName varchar(20) '@EmpName' , EmpSalary varchar(20) '@EmpSalary')
sp_xml_removedocument
Removes the internal representation of the XML document specified bythe document handle and invalidates the document handle. To avoidrunning out of memory, will run sp_xml_removedocument to free up thememory.
For more details, visit http://blogger.code4asp.net/2009/08/12/bulk-insert--sql-server.aspx
|
No responses found. Be the first to respond and make money from revenue sharing program.
|