2

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

Timwi
  • 65,159
  • 33
  • 165
  • 230
Geeth
  • 5,282
  • 21
  • 82
  • 133
  • Which database are you speaking from? Sql Server? – BitKFu Aug 30 '10 at 06: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? – Albin Sunnanbo Aug 30 '10 at 06:19
  • I want to export data everyday. I have created a suitable table. sql server 2008. – Geeth Aug 30 '10 at 06:23
  • 1
    possible duplicate of [.NET: How to insert XML document into SQL Server](http://stackoverflow.com/questions/3574836/net-how-to-insert-xml-document-into-sql-server) – Timwi Aug 30 '10 at 06:31
  • Hi Timwi, In that post there no mark of answer. Thats y i have posted my question here. – Geeth Aug 30 '10 at 06:35

2 Answers2

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();
    }
}
Community
  • 1
  • 1
BitKFu
  • 3,649
  • 3
  • 28
  • 43
  • Is it possible to filter the fields from the xml file while passing it as the parameter to insert? – Geeth Aug 30 '10 at 09:35
  • you could add a xpath query to filter the xml file. – BitKFu Aug 30 '10 at 10:56
  • Can you please explain a bit more please. – Geeth Aug 30 '10 at 10:59
  • Yes, you can do that with the XPathNavigator for example. XPathNavigator nav = xmlToSave.CreateNavigator(); string xml = nav.SelectSingleNode("/catalog/cd[title='Manowar']").InnerXml; – BitKFu Aug 30 '10 at 13:44
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.

KuldipMCA
  • 3,079
  • 7
  • 28
  • 48
  • The answer itself should contain the solution, not only link to it (as links become unaccessible after time) – Marki555 Jul 06 '15 at 13:11