Jeroen Mostert wrote:
ti*********@gmail.com wrote:
>Hi group,
In my database, I have a table with fields like this :
id | title | xml
------------------------------------
1 | title1 | <datas><data><item name="item1">value1</
item><item name="item2">value2</item></data></datas>
.....
I would like to filter my xml data, when it contains a item attribute
named item1
I'm using Linqpad with a request like that :
from i in myTable
where
i.xml.Descendants("data").Element("item").Attribu te("name").Value ==
"item1"
select i
but, linqpad tells me that :
'System.Collections.Generic.IEnumerable<System.Xm l.Linq.XElement>'
does not contain a definition for 'Element' and no extension method
'Element' accepting a first argument of type
'System.Collections.Generic.IEnumerable<System.Xm l.Linq.XElement>'
could be found (press F4 to add a using directive or assembly
reference)
It's telling you that "Descendants" will give you a collection of
elements, and you're trying to call "Element" on this collection (which
only applies to single elements).
This would work:
where i.xml.Descendants("data").Descendants("item").Any( item =>
(string) item.Attribute("name") == "item1")
So would this (using XPath):
where i.xml.XPathSelectElements("/data/item[@name=\"item1\"]").Any()
....except that neither of these work if "myTable" is an actual SQL table,
because LINQ to SQL can't handle XML methods. This is quite unfortunate,
because SQL Server 2005 does have support for querying XML. You can get
around it by materializing the query results and working with that, of course:
from i in myTable.ToArray()
where i.xml.XPathSelectElements("/data/item[@name=\"item2\"]").Any()
select i
But this has the huge drawback of pulling in all the records for selecting
on the client side, which rather defeats the point of querying.
If your table will never become big enough for this to matter, it might be
acceptable. Otherwise, you could factor out the XML selection logic to the
database. For example:
CREATE FUNCTION dbo.SelectByItemName(@name AS NVARCHAR(MAX))
RETURNS TABLE
RETURN
SELECT id, title, xml FROM myTable
WHERE xml.exist('/datas/data/item[@name=sql:variable("@name")]') = 1;
You can then use LINQ to XML in the designer to create a mapping to this
function.
The obvious drawback to this method is that you need to carefully work out
your data needs in advance as far as the SQL selection is concerned. And if
you can do that, you may want to consider avoiding XML altogether and using
a pure relational database, which cooperates much more nicely with most data
access technologies.
--
J.
