locked
Querying SQL Entity with XML datatype RRS feed

  • Question

  • i have a SQL table that contains a BookingPacket field, that has its datatype as XML

    is there anyway to query this via the entityframework 4.0?

    i have tried both as string & xml but no joy!

    i need something along these lines:-

            public static int GetBookingCountByBookingPacketNotProcessed(string bookingPacket)
            {
                XmlReader xmlReader =  XmlReader.Create(new System.IO.StringReader(bookingPacket));
                
                System.Data.SqlTypes.SqlXml xml = new System.Data.SqlTypes.SqlXml(xmlReader);
    
    
                using (var context = new BandBHoldingEntities())
                {
                    return context.Bookings.Where(b => b.BookingPacket == xml.Value && b.BookingReconciled == false).Count();
                }
         
            }

    Wednesday, March 28, 2012 1:39 PM

Answers

  • had to settle for creating Xdocuments and comparing an element value but would like to know if there is a more efficent method as per my original post

            public static int GetBookingCountByBookingPacketNotProcessed(string bookingPacket)
            {
    
    
                XDocument xmlnewBooking= XDocument.Parse(bookingPacket);
    
                string newBookingRef = xmlnewBooking.Element("booking").Attribute("ref").Value;
    
                using (var context = new BandBHoldingEntities())
                {
                    foreach (Booking booking in context.Bookings.ToList())
                    {
                        if (booking.BookingPacket.ToString() != string.Empty)
                        {
                            XDocument xmlexistingBooking= XDocument.Parse(booking.BookingPacket);
                            string existingBookingRef;
                            if (xmlexistingBooking.Element("booking") != null)
                            {
                                existingBookingRef = xmlexistingBooking.Element("booking").Attribute("ref").Value;
    
                                if (newBookingRef == existingBookingRef && booking.BookingReconciled == false)
                                    return 1;
                            }
                        }
                            
                    }
                }
    
                return 0;
         
            }


    • Edited by Craig G Thursday, March 29, 2012 1:55 PM
    • Marked as answer by Craig G Tuesday, April 3, 2012 9:07 AM
    Thursday, March 29, 2012 9:47 AM

All replies

  • had to settle for creating Xdocuments and comparing an element value but would like to know if there is a more efficent method as per my original post

            public static int GetBookingCountByBookingPacketNotProcessed(string bookingPacket)
            {
    
    
                XDocument xmlnewBooking= XDocument.Parse(bookingPacket);
    
                string newBookingRef = xmlnewBooking.Element("booking").Attribute("ref").Value;
    
                using (var context = new BandBHoldingEntities())
                {
                    foreach (Booking booking in context.Bookings.ToList())
                    {
                        if (booking.BookingPacket.ToString() != string.Empty)
                        {
                            XDocument xmlexistingBooking= XDocument.Parse(booking.BookingPacket);
                            string existingBookingRef;
                            if (xmlexistingBooking.Element("booking") != null)
                            {
                                existingBookingRef = xmlexistingBooking.Element("booking").Attribute("ref").Value;
    
                                if (newBookingRef == existingBookingRef && booking.BookingReconciled == false)
                                    return 1;
                            }
                        }
                            
                    }
                }
    
                return 0;
         
            }


    • Edited by Craig G Thursday, March 29, 2012 1:55 PM
    • Marked as answer by Craig G Tuesday, April 3, 2012 9:07 AM
    Thursday, March 29, 2012 9:47 AM
  • Hi Craig G,

    Welcome to MSDN Forum.

    I'm not clear about the question. Could you please show the XML and clarify what you want to query out?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, March 30, 2012 5:22 AM
  • the question is how do you query an Entity (based upon a SQLServer table) using LINQ that contains a field that has its datatype as XML, as in the SQL Server datatype XML

    Cheers

    Monday, April 2, 2012 9:21 AM
  • Hi Craig;

    Entity Framework doesn't support SQL data type of XML but returns that data type as a String data type. So doing what you did is about the only way to do it.

      


    Fernando (MCSD)

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

    Monday, April 2, 2012 1:58 PM