How to generate a query to ms sql based on an xml file?

Question

C++ / C#

The task is as follows - based on the xml and xsd file, you need to create a database in ms sql and put information from the xml file there (xml and xsd are not known in advance). Because the size of xml is not known in advance, then using DataSet.ReadXml is problematic, an out-of-memory error is likely.
The most optimal thing that we managed to find is the use of XmlReader, but since reading goes sequentially from top to bottom, then there is a problem in generating an INSERT query - the idea is that if the node contains elements with values ​​and these elements are of the same nesting level, then we form an insert request these values ​​into a table named "parent" element.

<a>
   <b>
      <c>val1</c>
      <d>val2</d>
   </b>
   <e>val3</e>
   <f>val4</f>
</a>

In this case, it is necessary to insert the values ​​bef into table a, and the values ​​c and d into table b. The database itself is now created based on DataSet.ReadXmlSchema and then for each DataTable I create a table in the database. Here is what has been written so far

while (xml.Read())
      {
       if (xml.NodeType.ToString() == "Element")
          {                    
              if (xml.IsEmptyElement != true)
                  {
                     myStack.Push(xml.Name);
                  }
          }
       else if (xml.NodeType.ToString() == "Text")
          {
              string first = myStack.Peek().ToString();
              myStack.Pop();
              string second = myStack.Peek().ToString();
              myStack.Push(first);
              Console.WriteLine("table {0} column {1} value {2}", second, first, xml.Value);
              string cmd = "INSERT INTO " + second + " (" + first + ") VALUES ('" + xml.Value + "')";
              SqlCommand command = new SqlCommand(cmd, conn);
              command.ExecuteNonQuery();
         }
     else if (xml.NodeType.ToString() == "EndElement")
         {
             myStack.Pop();
         }
     }

But here there is only one value in one line, which is accordingly not correct.

Answer 1

IMHO inexperienced June, read carefully.
1) Working with LINQ to XML
2) LINQ to XML Queries
In Jon Skeet's book, I read about LINQ to XML and the subtleties of its work, I looked, 3rd edition page 380.
You can request a memory area from the system in advance.
It is better to separate XML reading and database operations, log errors without stopping work, lack of memory - buy more, or something is wrong with the architecture.