How to Retrive XMLdata into database Table

[chinni1]

Hi , I want to retrive the xml data into my database table. The xml file is from outside of application.so i want to retrive that xml data into database with in some temporary table Thanq

[Vimalsoft Pty Ltd]

see this

public void Import_Data(String XMLFILE_Parth)
{

        con = new SqlConnection(StrconImport); //Your connection declared somewhere in the Class :)

        DataSet ds = new DataSet();    //Your dataset to do the final dumping

        ds.ReadXml(XMLFILE\_Parth);     //sending the it to the Dataset

        SqlBulkCopy sbc = new SqlBulkCopy(con); //object of bulkcopy and put in your connection object inside it

        try
        {
            con.Open();  //open the connection

            sbc.DestinationTableName = "dbo.XML\_Resources\_Import\_temp";  //You must have this table on the SQL , match the Columns

            sbc.WriteToServer(ds.Tables\[0\]); //Send the Data from the Dataset to the table 


        }
        catch (SqlException)
        {
            throw;
        }
        finally
        {

            
            if (con != null)
            {
                con.Close();
            }
        }

    }

WOW simple neeeeeeeeeeee :)

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.tiyaneProperties.co.za vuyiswa@its.co.za www.ITS.co.za

[Wendelius]

You can use OPENROWSET[^] function. Something like:

SELECT *
FROM OPENROWSET(BULK 'path\file', SINGLE_BLOB) AS alias

You can place the result into a variable and then use CONVERT[^] to convert the data to XML (or you can do the convert in the select statement). After that you can insert the data into the table. If you want, you can compress this to one statement, but it will be harder to find errors if such occur.

INSERT INTO targetTable (targetColumn)
SELECT CONVERT(xml, alias.sourcecolumn)
FROM OPENROWSET(BULK 'path\file', SINGLE_BLOB) AS alias

The need to optimize rises from a bad design.My articles[^]