BULK insert in sql server from c# via XML

  

AS TEXT NO NEED TO SAVE ANY FILE...

private void button1_Click(object sender, EventArgs e)

        {

            DataSet dsData = GetDataSet();

            String xmlData = ConvertDataTableToXML(dsData.Tables[0]);

            SqlConnection conn = new SqlConnection("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");

            SqlCommand command = new SqlCommand("sp_InsertData '" + xmlData + "'", conn);

conn.Open();

           

            command.ExecuteNonQuery();

            conn.Close();

        }

        private static DataSet GetDataSet()

        {

            DataSet ds = new DataSet();

            DataTable dt = new DataTable("Table");

            dt.Columns.Add("Name", Type.GetType("System.String"));

            dt.Columns.Add("Address", Type.GetType("System.String"));

            dt.Columns.Add("Phone", Type.GetType("System.String"));

            DataRow dr = dt.NewRow();

            dr["Name"] = "Franklin";

            dr["Address"] = "Hosur";

            dr["Phone"] = "46945616";

            dt.Rows.Add(dr);

            dr = dt.NewRow();

            dr["Name"] = "SAMRAj Gill";

            dr["Address"] = "DT-HOSUR";

            dr["Phone"] = "78971651";

            dt.Rows.Add(dr);

            ds.Tables.Add(dt);

            return ds;

        }

        private static string ConvertDataTableToXML(DataTable dtData)

        {

            DataSet dsData = new DataSet();

            StringBuilder sbSQL;

            StringWriter swSQL;

            string XMLformat;

            try

            {

                sbSQL = new StringBuilder();

                swSQL = new StringWriter(sbSQL);

                dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);

                dsData.Tables[0].TableName = "SampleDataTable";

                foreach (DataColumn col in dsData.Tables[0].Columns)

                {

                    col.ColumnMapping = MappingType.Attribute;

                }

                dsData.WriteXml(swSQL, XmlWriteMode.WriteSchema);

                XMLformat = sbSQL.ToString();

                return XMLformat;

            }

            catch (Exception sysException)

            {

                throw sysException;

            }

        }

//////////////STORED PROCEDURE ////////

USE [TestDBbyAshwinSh]

GO

/****** Object:  StoredProcedure [dbo].[sp_InsertData]    Script Date: 04/20/2017 15:44:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_InsertData]

(@xmlString VARCHAR(MAX))

AS

BEGIN

    DECLARE @xmlHandle INT

   

  

    DECLARE @stagingTable TABLE

    (

        [Name] VARCHAR(50),

        [Address] VARCHAR(50),

        [Phone] VARCHAR(50)

    )

   

    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString

   

  

    INSERT INTO @stagingTable

    SELECT   [Name] ,

        [Address],

        [Phone]

    FROM OPENXML (@xmlHandle, '/NewDataSet/SampleDataTable',1)

        WITH ([Name] varchar(50) '@Name',

            [Address] varchar(50) '@Address',

            [Phone] varchar(50) '@Phone'

             )

    INSERT INTO SampleData ([Name],

            [Address],

            [Phone])

    (SELECT [Name] ,

        [Address],

        [Phone]

    FROM @stagingTable)

    EXEC sp_xml_removedocument @xmlHandle

END

REFERENCE LINK in code project

========new experience========
DECLARE @SET1 NVARCHAR(MAX)

SET @SET1 ='<NewDataSet>

  <SampleDataTable ReferenceType="2 Opportunity" ReferenceId="10241" ReferenceUrl="https://jquery.com/" />
  <SampleDataTable ReferenceType="3 Opportunity" ReferenceId="12546" ReferenceUrl="http:/" />
  <SampleDataTable ReferenceType="Lorem ipsum" ReferenceId="74158" ReferenceUrl="http:///" />
  <SampleDataTable ReferenceType="Demo Ref type" ReferenceId="84766" ReferenceUrl="#" />
  <SampleDataTable ReferenceType="1 Opportunity" ReferenceId="85263" ReferenceUrl="#" />
  <SampleDataTable />
</NewDataSet>'


DECLARE @XMLDocPointer INT
  

  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @SET1
  BEGIN TRANSACTION   
    
    
--INSERT INTO dbo.IMDb_ProjectReferences     (ProjectID ,ReferenceType, ReferenceID ,ReferenceURL)
         select '1', ReferenceType ,ReferenceId ,ReferenceUrl
           from    
 OPENXML(@XMLDocPointer,'/NewDataSet/SampleDataTable',1)with(
ReferenceType  NVARCHAR(100) ,ReferenceId BIGINT ,ReferenceUrl NVARCHAR(100) ) 

EXEC sp_xml_removedocument @XMLDocPointer