How to insert IEnumerable<XElement> object into SQL Server table?

Question

I have created below code to get Users from XML:

        string userName = "user";
        string password = "password";
        string uri = @"uri";
        WebClient webClient = new WebClient();
        webClient.Credentials = new NetworkCredential(userName, password);  
        Stream stream = webClient.OpenRead(uri);
        XElement output = XElement.Load(stream);
        IEnumerable<XElement> users = from el in output.Elements() select el;

        foreach (XElement str in users)
        {
            Console.WriteLine(str);
        }

Result:

        <User>
          <ObjectId>3cbde</ObjectId> 
          <Alias>alias</Alias> 
          <DisplayName>Display Name</DisplayName> 
          <TimeZone>0</TimeZone> 
          <Language>0</Language> 
          <ListInDirectory>false</ListInDirectory> 
          <IsVmEnrolled>true</IsVmEnrolled> 
        </User>

Now I would like to insert the data into database, but I would like to put each field as separate column - table in database has columns: ObjectId, DisplayName, TimeZone and etc. Could you please give me some advices how it can be easily done? Do I need to convert it into DataTable or else?

Answer 1

In this example, I'm taking the XML stream locally, for testing purpose. Just change it to your method of getting the stream (WebClient). Also used SqlBulkCopy, I don't see any downsides of doing so plus, if you end up having a lot of rows to insert, that'll be faster. There are probably better ways to refactor this, but roughly:

        SqlBulkCopy bulkCopy = new SqlBulkCopy("ConnectionString...");
        bulkCopy.DestinationTableName = "PhysicalTableName";
        DataTable dt = new DataTable("PhysicalTableName");


        dt.Columns.Add("ObjectId");
        dt.Columns.Add("Alias");
        dt.Columns.Add("DisplayName");
        dt.Columns.Add("TimeZone");
        dt.Columns.Add("Language");
        dt.Columns.Add("ListInDirectory");
        dt.Columns.Add("IsVmEnrolled");


        XElement output = XElement.Load("c:\\temp\\input.xml");
        IEnumerable<XElement> users = output.Elements();

        foreach (XElement str in users)
        {
            DataRow dr = dt.NewRow();
            foreach (XElement node in str.Elements())
            {
                dr[node.Name.LocalName] = node.Value;
            }

            dt.Rows.Add(dr);
        }

        bulkCopy.WriteToServer(dt);

I did not have any information on the schema of your tables so by default in this example, all the columns are of type String.

Do not forget about exception handling as well.