如何使用Linq to SQL和Linq to XML查询数据库列中的XML数据? [英] How to query XML data in database column using Linq to SQL and Linq to XML?

问题描述

XML:

<root>
    <item>
        <href>http://myurl</href>
    </item>
    <item>
        <href>http://myurl2</href>
    </item>
</root>

XML数据存储在数据库表中.

The XML data is stored in a database table.

我可以建立一个Linq查询来选择行，提取XML，然后例如提取所有 href 标记吗?最终结果将是所有选定行的所有URL的列表.

Can I build a Linq query which selects the rows, extracts the XML and then, for example, extracts all the href tags? The end result would then be a list of all URLS for all selected rows.

这是我的尝试，但没有给我我想要的-这将是所有选定用户的所有href的列表.我只得到一个空的IEnumerations列表.

This is my attempt, but it is not giving me what I want - which would be a list of all hrefs for all selected users. I just get a list of empty IEnumerations.

var all = from bm in MYTABLE
        select new { name=bm.SPP_USER_ID, xml=(string) bm.SPP_BOOKMARKS_XML};

var docs = from x in all
        select XDocument.Parse(x.xml);
var href = from h in docs
        select h.Descendants("href");

---

解决方案

有2个问题.

-我猜只有在实际需要结果时才执行查询.当我从SQL进行到XML查询时，结果查询变成了SQL和XML的混合，因此无法执行.我的解决方案是通过将SQL查询转换为结果列表来强制结果.这将linq-sql与linq-xml分开了.

-- I guess that the query is only executed when a result is actually demanded. As I progressed from an SQL to an XML query, the resulting query became a mix of SQL and XML and thereby unexecutable. My solution was to force a result by converting the SQL query to a result list. This separated the linq-sql from linq-xml.

var docs = from x in all
        select XDocument.Parse(x.xml);
var docs2 = docs.ToList();  // force result

-第二个问题是我忘记将名称空间添加到XML查询中.完成后，我得到了所需的结果

-- The second problem was that I forgot to add the namespace to my XML query. Once I did that I got the required result

XNamespace ns = "http://acme/bookmarks";
var href = from h in docs2
    select h.Descendants(ns + "href");

感谢您的所有帮助！

推荐答案

以下是我尝试过的几件事.

Here are the couple of things I tried.

1. 设置:我用表MYTABLE创建了一个数据库，该表包含2列.第1列(SPP_USER_ID)为varchar(255)，第2列(SPP_BOOKMARKS_XML)为xml数据类型.我添加了2行，以反映mellamokb使用的行.我创建了LINQ to SQL设计图并将其拖到该表中.我的应用程序只是一个控制台应用程序，可将LINQ加载到SQL数据上下文中，然后调用您的命令.

2. 我直接针对数据表查询，如下所示:

1. Setup: I created a database with a table MYTABLE, containing 2 columns. Column 1 (SPP_USER_ID) is varchar(255), column 2 (SPP_BOOKMARKS_XML) is xml data type. I added 2 rows, mirroring those that mellamokb used. I created a LINQ to SQL design surface and dragged in this table. My app is just a console app that loads the LINQ to SQL data context and then calls your commands.

2. I queried directly against the data table, as follows:

        var all = from bm in context.MYTABLEs
                  select new { name = bm.SPP_USER_ID, xml = (string)bm.SPP_BOOKMARKS_XML };

        var docs = from x in all
                   select XDocument.Parse(x.xml);

        var href = from h in docs
                   select h.Descendants("href");

结果是IEnumerables的集合. IE.对于数据库中的每一行，我都有一个IEnumerable，其中包含所有"href"后代.因此，在这种情况下，我得到了两个结果；第一个结果是包含2个元素的IEnumerable，第二个结果是包含1个元素的IEnumerable.从您的描述看来，这似乎至少接近您想要的.但是，可能由于查询的结构方式而看不到期望的结果.

The result was a collection of IEnumerables. I.e. for each row in the database, I got an IEnumerable containing all of the "href" descendants. So in this scenario, I got two results; the first result was an IEnumerable that contained 2 elements, and the second result was an IEnumerable that contained 1 element. It seems like, from your description, this is at least close to what you want. However, you maybe aren't seeing what you expect just because of the way your query is structured.

请注意，有时很难获得Visual Studio来真正向您显示这样的查询结果.我发现我经常不得不观察"该项目两次，然后才开始触发结果枚举.

Note that it is rather difficult sometimes to get Visual Studio to actually show you the results of a query like this. I've found that I frequently have to "watch" the item twice before it bothers to fire the result enumeration.

我试图复制您的OP中编写的代码，如下所示:

I tried to duplicate the code written in your OP as follows:

        var MYTABLE = (from bm in context.MYTABLEs select bm).ToList();

        var all = from bm in MYTABLE
                  select new { name = bm.SPP_USER_ID, xml = (string)bm.SPP_BOOKMARKS_XML };

        var docs = from x in all
                   select XDocument.Parse(x.xml);

        var href = from h in docs
                   select h.Descendants("href");

当我这样做时，由于"xml"值已经是XDocument(?)了，因此导致"xml"值被删除，因此将其强制转换为字符串会导致所有标记被剥离.因此，接下来的两个语句由于XML解析异常而失败.

When I did this, it resulted in the "xml" value being trashed because it was already an XDocument (?) so casting it to string resulted in all of the tags being stripped out. So, the next two statements failed with an XML parse exception.

这篇关于如何使用Linq to SQL和Linq to XML查询数据库列中的XML数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！