Creating SQL table from XML using C#

Question

Looking for help with the following code - I am trying to figure out where to code in the XML file I actually want to use to move to SQL table. I'm just not sure how to specify it from my file system...

using System;
using System.Data.SqlClient;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SqlConnection("Server=(local);Database=TempDb;Integrated Security=true"))
            {
                con.Open();

                var cmdCreateTable = new SqlCommand("if object_id('dbo.t') is null create table t(id int identity primary key, doc xml);",con);
                cmdCreateTable.ExecuteNonQuery();

                var cmdInsertXml = new SqlCommand("insert into t(doc) values (@doc);", con);
                var pDoc = cmdInsertXml.Parameters.Add("@doc", System.Data.SqlDbType.Xml);

                var doc = XDocument.Parse("<root><cn/><cn/><cn/></root>");
                pDoc.Value = doc.CreateReader();

                cmdInsertXml.ExecuteNonQuery();

                var cmdRetrieveXml = new SqlCommand("select id, doc from t",con);
                using (var rdr = cmdRetrieveXml.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var xr = rdr.GetSqlXml(1);
                        var rd = XDocument.Parse(xr.Value);
                        Console.WriteLine(rd.ToString());

                    }
                }
            }
        }
    }
}

So is this a question about how to create a parameter of type xml from a file? If so the rest of the example is pretty much noise, and the question doesn't reflect the real question. — TomC, Jun 11 '19 at 23:58

score 1 · Answer 1 · answered Jun 12 '19 at 00:00

I'm not sure I've the right, but i found this if you want to try it.

try to read xml from DataTable

DataTable table;

   private void createDatatableFromXML()
   {
       table = new DataTable();
       string dataFile = @"DatafileLocation\datafile.xml";
       if (File.Exists(dataFile))
       {
           table.ReadXml(dataFile);

       }
       else
       {
          //Do som messaging
           return;
       }
   }

After that try to create SQL table from DataTable see this link.

score 1 · Answer 2 · answered Jun 12 '19 at 00:35

1

FRom the code mentioned I Would say the line below needs to be updated var doc = XDocument.Parse("");

I would say something like var doc = XDocument.Parse(File.ReadAllText(filePath));

This would only work if the file is in the correct format.

answered Jun 12 '19 at 00:35

Ron

421
3
9

Creating SQL table from XML using C#

2 Answers2