Bulk Insert into SQL Server Table With XML

Hey There,

Here, is the example of Bulk Insert into SQL Server Table.

From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.

Example SP.

CREATE PROCEDURE StoredProcName

(

@.strXML varchar(8000)

)

AS

Declare @.intPointer int

exec sp_xml_preparedocument @.intPointer output, @.strXML

INSERT into tbl_plnd_insertion

SELECT Column1, Column2, Column3, Column4, Column5

FROM OpenXml(@.intPointer,'/root/tbl_plnd_insertion',2)

WITH (Column1 varchar(20) '@.Column1' , Column2 varchar(20) '@.Column2', Column3 varchar(20) '@.Column3' , Column4 varchar(50) '@.Column4', Column5 varchar(50) '@.Column5')

exec sp_xml_removedocument @.intPointer

Thanks !!!!!

In SQL Server 2000, Text data type is reasonable to use for huge data,

@.strXML Text--varchar(8000)

This is not a bulk insert, it is reading the data from the XML string into a table. sp_xml_preparedocument is expensive, it will use lot of memory & resource when the HUGE data parsed.

In SQL Server 2005, the best option is using XML datatype. You don’t need expensive sp_xml_preparedocuemnt to parse your XML string into selectable values, no need OpenXML also.

Sample,

Code Snippet

Declare @.xml as XML

set @.xml = '<root><student name="Stud1" sex="m"/><student name="Stud2" sex="f"/></root>'

Select

[Table].[Column].value('./@.name', 'varchar(100)') as [Name],

[Table].[Column].value('./@.sex', 'char') as [Sex]

from

@.xml.nodes('/root/student') as [Table]([Column])

|||

Hey

I am really appreciating your input… J

I did not describe all details in my code.

Thanks