Map XML Nodes to Datatable Fields

Question

I am new to C# so I apologize if this is a simple task. What I would like to do open an XML file whose root node is a table name and the children of the root node are field names and values. I would then like to map the fields to the root node's table in a SQL Server database and update or insert as needed. Does anyone know if there is a more elegant way to do this than looping through the node tree and building out the SQL string? It seems like there should be a way to bind the fields as if the XML document were a form, only it would only exist in memory. Again, sorry if this question has an obvious answer. Thanks in advance for any help.

Answer 1

The simplest way would probably be to create a simple entity class for your xml types, and use the XML Serialization attributes to map your xml elements to your classes and its properties. You can then use the standard XmlSerializer to deserialize xml to an object, or serialize an object to xml. Once you have an object, it should be easy enough to use an OR mapper (LINQ to SQL, Entity Framework, NHibernate) or something like iBatis to perform your updates/inserts/deletes with your entity classes.

Answer 2

I use OPENXML method to solve such tasks. There is example from BOL how to select the data from XML:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')

So, you can use INSERT .. SELECT FROM OPENXML statement to insert this data to DB.