import xml data in sql server through asp.net

[satishv96]

DECLARE @results table (result xml)
DECLARE @xml XML
SELECT @xml =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK 'D:\xmlfile\abc\Technicle1021012.xml', SINGLE_BLOB)
AS x
select @xml



i want to stor this xml data in our sql database
so help me

[PsychoCoder]

If you're wanting to do this in a .NET language then take that SQL code, make a stored procedure then call it from code. Here's an example of what I'm describing:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ImportXML 
    -- Add the parameters for the stored procedure here
    @FileContents VARCHAR(4000)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @results table (result xml)
    DECLARE @xml XML
    SELECT @xml =CONVERT(XML, bulkcolumn, 2)
    FROM OPENROWSET(BULK @FileContents, SINGLE_BLOB) 
    AS x
    select @xml
END
GO
 

And the code for executing it

public void ImportSql()
{
    SqlConnection conn = new SqlConnection("YourConnectionString");
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = "ImportXML";
    cmd.CommandType = CommandType.StoredProcedure;
 
    cmd.Parameters.AddWithValue("@FileContents", "D:\xmlfile\abc\Technicle1021012.xml");
    cmd.ExecuteNonQuery();
}
 

NOTE: There may be some changes that need to be made, I wrote this example in Notepad but it should get you started down the right path.