none
Working with an xml column RRS feed

  • Question

  • I have recently started playing around with Linq to Sql,

    And I have been trying to find the syntax required to query an xml typed column.

     

    So far, the only solution I found is to query the xml column using XQuery through a stored proc. Then to call this stored proc via Linq to Sql and go from there.


    That or to use Linq to Sql to select depending on other columns (if any) and use Linq to XML afterwards, but this solution does not seem optimal.


    Has anyone found another way of working with xml column and linq to sql?


    Regards,

    Tuesday, March 4, 2008 10:24 AM

Answers

  • I've been looking at this problem myself today. There is no Linq to XQuery (yet?):

    According to Scott Guthrie (http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx#2691097):

     XML datatypes in SQL are represented as strings with LINQ to SQL Entities.  You could use XLINQ to query on an XML column within your LINQ to SQL entitiy - but this querying would happen in your middle-tier (within ASP.NET).  You can't execute a remote XQuery against the database and filter returned results based on that in the first release.
     
    I've found a different approach, that might be helpful to someone:
     
    Code Snippet
    using (MyDataContext dc = new MyDataContext())
    {
        var q = dc.ExecuteQuery<orderxml>("SELECT * FROM [dbo].[orderxml] AS [t0] WHERE [t0].[orderlines].value('(//artnr)[1]', 'varchar(1000)') = @p0", "124050116");
        foreach (orderxml c in q)
        {
            c.orderlines.Element("OrderDetail").Element("quantity").Value = "99";
            c.orderlines.SetAttributeValue("lastupdated", DateTime.Now.ToLongTimeString());
            c.orderlines = new XElement(c.orderlines);  //without this, linq does not see the changes, and no update sql is generated
        }
        dc.SubmitChanges();
    }

     

    The "orderxml" table has a column "orderlines" with the xml datatype. Using ExecuteQuery() allows you to simply give the Transact SQL statement, and since no translation is needed, you can use XQuery. The querying is done on the server, which is far more efficient than in your middle-tier.
     
    Note that for proper concurrency checking you need to include a timestamp column in the "orderxml" table.
    Friday, April 4, 2008 9:18 PM
  • see thread : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2166966&SiteID=1
    Linq to Sql version 1 does not have XQuery support.

    Regards,
    Tuesday, March 4, 2008 11:05 AM

All replies

  • see thread : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2166966&SiteID=1
    Linq to Sql version 1 does not have XQuery support.

    Regards,
    Tuesday, March 4, 2008 11:05 AM
  • I've been looking at this problem myself today. There is no Linq to XQuery (yet?):

    According to Scott Guthrie (http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx#2691097):

     XML datatypes in SQL are represented as strings with LINQ to SQL Entities.  You could use XLINQ to query on an XML column within your LINQ to SQL entitiy - but this querying would happen in your middle-tier (within ASP.NET).  You can't execute a remote XQuery against the database and filter returned results based on that in the first release.
     
    I've found a different approach, that might be helpful to someone:
     
    Code Snippet
    using (MyDataContext dc = new MyDataContext())
    {
        var q = dc.ExecuteQuery<orderxml>("SELECT * FROM [dbo].[orderxml] AS [t0] WHERE [t0].[orderlines].value('(//artnr)[1]', 'varchar(1000)') = @p0", "124050116");
        foreach (orderxml c in q)
        {
            c.orderlines.Element("OrderDetail").Element("quantity").Value = "99";
            c.orderlines.SetAttributeValue("lastupdated", DateTime.Now.ToLongTimeString());
            c.orderlines = new XElement(c.orderlines);  //without this, linq does not see the changes, and no update sql is generated
        }
        dc.SubmitChanges();
    }

     

    The "orderxml" table has a column "orderlines" with the xml datatype. Using ExecuteQuery() allows you to simply give the Transact SQL statement, and since no translation is needed, you can use XQuery. The querying is done on the server, which is far more efficient than in your middle-tier.
     
    Note that for proper concurrency checking you need to include a timestamp column in the "orderxml" table.
    Friday, April 4, 2008 9:18 PM