none
Approach for handling XML to SQL with EF? Ling to XML + Linq to Entities? RRS feed

  • Question

  • Say I had an XML document I read from a string or an xml field of a SQL table.  I want to read that data into 2 other tables, and the xml data has parent-child relationships.  Could I read the xml data with linq to xml then create the entities and then save changes?
    Thursday, December 12, 2013 5:27 PM

Answers

  • Hello shiftbit,

    >> Could I read the xml data with linq to xml then create the entities and then save changes?

    The answer is yes. For this I made an example and please have a look at it below:

    My XML file is like below. Order and OrderDetails are entities which have a one-to-many relationship.

    <?xml version="1.0" encoding="utf-8"?>
    
    <Order>
    
      <OrderCode>1</OrderCode>
    
      <OrderDetails>
    
        <OrderDetail>
    
          <Order nil="true" />
    
          <OrderDetailCode>1</OrderDetailCode>
    
          <OrderDetailID>1</OrderDetailID>
    
          <OrderDetailName>1111111111</OrderDetailName>
    
          <OrderID>1</OrderID>
    
        </OrderDetail>
    
        <OrderDetail>
    
          <Order nil="true" />
    
          <OrderDetailCode>1</OrderDetailCode>
    
          <OrderDetailID>2</OrderDetailID>
    
          <OrderDetailName>1</OrderDetailName>
    
          <OrderID>1</OrderID>
    
        </OrderDetail>
    
      </OrderDetails>
    
      <OrderID>1</OrderID>
    
      <OrderName>1111111111</OrderName>
    
    </Order>
    

    The query codes:

    XDocument doc = XDocument.Load("E:\\BMX\\File\\2013-12\\Order.xml");
    
                    var result = (from order in doc.Elements("Order")
    
                                  select new Order()
    
                                 {
    
                                     OrderID = Convert.ToInt32(order.Element("OrderID").Value),
    
                                     OrderCode = order.Element("OrderCode").Value,
    
                                     OrderName = order.Element("OrderName").Value,
    
                                     OrderDetails = (from od in order.Elements("OrderDetails").Elements("OrderDetail")
    
                                                     select new OrderDetail()
    
                                                     {
    
                                                         OrderDetailID = Convert.ToInt32(od.Element("OrderDetailID").Value),
    
                                                         OrderDetailCode = od.Element("OrderDetailCode").Value,
    
                                                         OrderDetailName = od.Element("OrderDetailName").Value,
    
                                                         OrderID = Convert.ToInt32(od.Element("OrderID").Value),
    
                                                     }).ToList()
    
                                 }).FirstOrDefault();
    

    The result:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 13, 2013 5:07 AM
    Moderator