none
Entity Framework Treats XML type in SQL Server as a string. RRS feed

  • Question

  • Hi: Is it possible to convert the string returned by Entity Framework from a SQL Server database xml field back to a XMLDocument object in C#?? Also from what I am reading you can use Linq to Xml on it. Would you know of any good examples or reference I can checkout? Or would it be better to use SQL Server stored procedures? As you can tell I am a little confused :-).

    Thanks Mike.


    Mike Gallinger C.Tech. Cutting Edge Computing Software Developer

    Tuesday, July 9, 2013 2:29 AM

Answers

  • Hi Mike;

    If you are using code first you can do something like this to get the XElement, Linq to XML.

    public class MyTable
    {
       public int Id { get; set; }
    
       [Column(TypeName="xml")]
       public string myXmlColumn { get; set; }
       
       [NotMapped]
       public XElement MyXmlColumn
       {
           get { return XElement.Parse(myXmlColumn); }
           set { myXmlColumn = value.ToString(); }
       }
    }
    If you are using Database First then place the below code in a partial class MyTable, don't use the one the designer created because the code below will be deleted when you update your model. 

       [NotMapped]
       public XElement MyXmlColumn
       {
           get { return XElement.Parse(myXmlColumn); }
           set { myXmlColumn = value.ToString(); }
       }
    Then when you need to set or get the value of the XML field use MyXmlColumn property. 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, July 9, 2013 3:50 AM

All replies

  • Hi,

    There is a solution in the answer to this thread. Using the unmapped property approach you could then set the property in the ObjectMaterialized event or have the logic in the entity class itself.

    Tuesday, July 9, 2013 2:48 AM
  • I think that Chase's reply is correct. But using  NotMapped  attribute is the simplest method.  We can declare an additional XmlDocument typed property and set NotMapped attribute on it, so it won't be mapped to db columns.   We can add some custom logic to convert values between the property XMLValues and MyDocument.  for example:

    public class MyXMLTable
        {
            [Key]
            public int MyID { get; set; }

            [Column(TypeName = "xml")]
            public string XMLValues { get; set; }

            XmlDocument _MyDocument;
            [NotMapped]
            public XmlDocument MyDocument
            {
                get
                {
                    if(_MyDocument==null)
                    {
                        _MyDocument= new XmlDocument();
                        _MyDocument.LoadXml(XMLValues);
                    }
                    return _MyDocument;
                }
            }
        }

    Tuesday, July 9, 2013 3:44 AM
  • Hi Mike;

    If you are using code first you can do something like this to get the XElement, Linq to XML.

    public class MyTable
    {
       public int Id { get; set; }
    
       [Column(TypeName="xml")]
       public string myXmlColumn { get; set; }
       
       [NotMapped]
       public XElement MyXmlColumn
       {
           get { return XElement.Parse(myXmlColumn); }
           set { myXmlColumn = value.ToString(); }
       }
    }
    If you are using Database First then place the below code in a partial class MyTable, don't use the one the designer created because the code below will be deleted when you update your model. 

       [NotMapped]
       public XElement MyXmlColumn
       {
           get { return XElement.Parse(myXmlColumn); }
           set { myXmlColumn = value.ToString(); }
       }
    Then when you need to set or get the value of the XML field use MyXmlColumn property. 

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, July 9, 2013 3:50 AM
  • Please explain what you mean by Code First or Database First? I think I have an idea but I am not sure.

    Mike Gallinger C.Tech. Cutting Edge Computing Software Developer

    Tuesday, July 9, 2013 9:48 PM
  • Do you have a sample I could look at?

    Mike Gallinger C.Tech. Cutting Edge Computing Software Developer

    Tuesday, July 9, 2013 10:26 PM
  • Hi Mike;

    To your question, "Please explain what you mean by Code First or Database First?", Code First allows you to define your model using POCO, Plain Old CLR Object, instead of using the modeling tool. You can then use these POCO classes to either map them to an existing database or use them to generate a database schema. Database First is where you already have a database on the server and you allow the modeling tool to create all the classes to map to the database which is fully automated.

    Sorry but I do not have an example of using the XML data type with Entity Framework. I di look on the web but could not find on either.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Wednesday, July 10, 2013 3:45 AM
  • Thanks. I think I solved my problem just by using the parse function to parse the string I get back from the database. Then I use the XDocument class.

    Thanks for all your help.


    Mike Gallinger C.Tech. Cutting Edge Computing Software Developer

    Saturday, July 13, 2013 7:39 PM
  • Hi Mike;

    Yes as I stated in my code that I posted.

      [NotMapped]
       public XElement MyXmlColumn
       {
           get { return XElement.Parse(myXmlColumn); }
           set { myXmlColumn = value.ToString(); }
       }

    I used Parse method with the value coming back from SQL server.

    Have a great day.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, July 13, 2013 7:48 PM
  • Hello Fernando,

    I used your method to get XML structure from Entity Frame. 
    Everything would be ok but.
    I need to do query for a row were a value in xml element from xml field in DB will be compared to some other value.

    for example (according to your example):

    using (MyEntities ent = new MyEntities())
    {
    	int myCounter = (from n in ent.MyTable where n.MyXmlColumn.Element("someElement").Value.Equals("something") select n).Count();
    }


    Is there solution to do this?

    Monday, May 12, 2014 6:50 PM
  • Hi PlushMasta;

    Because XML data is stored in SQL server as a String you can not query the column on the server as if it were an XDocument. So you would have to bring the rows back from the database and then query the column on the local system.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, May 13, 2014 3:05 AM
  • @Fernando: This is wrong. SQL Server has XML data types for quite some time now.

    http://msdn.microsoft.com/en-us/library/hh403385.aspx indicates SQL Server 2012. It also has a XQuery language extension to query these types intelligently and a special index. It internally deconsctructs the XML into the parts.

    I strongly suggest you try reading the documentation for products you give advice for at times. Maybe every couple of years.

    Monday, August 4, 2014 5:25 PM
  • @JustTom; please note the title of the question, "Entity Framework Treats XML types in SQL Server as a string", Entity Framework does not support native XML as a data type. When you map an XML column in a SQL table Entity Framework maps it to a String data type. 


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, August 4, 2014 8:19 PM
  • You have XElement what about XDocument when you have an whole file that is stored in the database

    Tuesday, January 26, 2016 3:20 PM
  • You have XElement what about XDocument when you have an whole file that is stored in the database

    You export the XML out of the XMLDocumnent to a string variable, and you save the string variable to a Entity string property and persist the object to the DB. You load the XMLDocumnent from the Entity string property that is holding the XML.
    Tuesday, January 26, 2016 4:16 PM
  • Hi mrp100461;

    You can use either one, just in the code I had posted change any occurrences of XElement to XDocument. Please read this web page, Querying an XDocument vs. Querying an XElement.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, January 26, 2016 4:19 PM