2

I want to export xml file data to sql database table. Can anyone guide me for this?

5
  • Which database are you speaking from? Sql Server? Commented Aug 30, 2010 at 6:16
  • Do you already have a database with a suitable table? Will you do this many times or just once? Which database do you use? Commented Aug 30, 2010 at 6:19
  • I want to export data everyday. I have created a suitable table. sql server 2008. Commented Aug 30, 2010 at 6:23
  • 1
    possible duplicate of .NET: How to insert XML document into SQL Server Commented Aug 30, 2010 at 6:31
  • Hi Timwi, In that post there no mark of answer. Thats y i have posted my question here. Commented Aug 30, 2010 at 6:35

2 Answers 2

4

If it's an SQL Server, I already answered a similar Question. Have a look at the following posting:

.NET: How to insert XML document into SQL Server

You can use that small c# part to store your data. You only have to amend the table and column fields.

class Program
{
    private static void SaveXmlToDatabase(DbConnection connection,
          XmlDocument xmlToSave)
    {
       String sql = "INSERT INTO xmlTable(xmlColumn) VALUES (@xml)";

       using (DbCommand command = connection.CreateCommand())
       {
          XPathNavigator nav = xmlToSave.CreateNavigator();
          string xml = nav.SelectSingleNode("/catalog/cd[title='Manowar']").InnerXml;

          command.CommandText = sql;
          command.Parameters.Add(
            new SqlParameter("@xml", SqlDbType.Xml) 
               {Value = new SqlXml(new XmlTextReader(xml
                           , XmlNodeType.Document, null)) });

          DbTransaction trans = connection.BeginTransaction();
          try
          {
             command.ExecuteNonQuery();
             trans.Commit();
          }
          catch (Exception)
          {
             trans.Rollback();
             throw;
          }
       }
    }

    static void Main(string[] args)
    {
        XmlDocument document = new XmlDocument();
        document.Load(args.First());

        SqlConnection connection = new SqlConnection(
            "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;");

        SaveXmlToDatabase(connection, document);

        connection.Close();
    }
}
Sign up to request clarification or add additional context in comments.

4 Comments

Is it possible to filter the fields from the xml file while passing it as the parameter to insert?
you could add a xpath query to filter the xml file.
Can you please explain a bit more please.
Yes, you can do that with the XPathNavigator for example. XPathNavigator nav = xmlToSave.CreateNavigator(); string xml = nav.SelectSingleNode("/catalog/cd[title='Manowar']").InnerXml;
1

check below link for this

http://www.simple-talk.com/sql/t-sql-programming/beginning-sql-server-2005-xml-programming/

you can find the solution.

1 Comment

The answer itself should contain the solution, not only link to it (as links become unaccessible after time)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.