XQuery - 在表中的 XML 列中搜索
请找到以下用于在数据库 xml 列中搜索关键字的查询
显示的查询用于从 SQL Server 中的 xml 列中搜索名称、单词或简介。使用的 CMS 是 Umbraco。
该查询最初是在数据有限的情况下设计的。
现在数据库有几百万条记录，查询超时取不到数据。 该查询还对搜索结果的检索顺序进行优先级排序 in if 返回Name字段数据搜索结果则返回CASE 1 else 根据返回的搜索数据，
SELECT Name,word,brief, */Selecting Values */
CASE
WHEN Name like '%' THEN 1 */Prioritizing Order in which data to be seen */
WHEN word like '%' THEN 2
WHEN brief like '%' THEN 3
END AS Search
from
(select
A.xml.value('(//@node)[1]','nvarchar(20)') as Name, /* XQuery */
A.xml.value('(//word)[1]','nvarchar(225)') as word,
A.xml.value('(//brief)[1]','nvarchar(max)')as brief
from
(Select Convert(xml, xml) AS XML
from [dbo].[cmsContentXml]) AS B
Cross Apply xml.nodes('//items/item') AS A(xml)) D
where ((Name like '%') /*Condition */
OR(word like '%')
OR(brief like '%'))
group by word,Name,brief
order by 3 ASC
请帮助提供优化或重写此查询以搜索 xml 列中的数据的解决方案。除了从列中检索数据之外。
当在前端单击搜索按钮而不输入任何关键字时，单击操作必须从数据库中检索所有项目。
提前致谢
Please find the following query that is being used to search for keyword in a database xml column
The shown query is being used to search for Name,word or brief from an xml column in SQL Server. The CMS in use is Umbraco.
The query was designed initially when the data was limited.
Now the database has millions of records and the query times out unable to fetch data.
The query also prioritizes the order in which the search results are retrieved as
in if the Name field data search result is returned the CASE 1 is returned else
depending on the search data is returned
SELECT Name,word,brief, */Selecting Values */
CASE
WHEN Name like '%' THEN 1 */Prioritizing Order in which data to be seen */
WHEN word like '%' THEN 2
WHEN brief like '%' THEN 3
END AS Search
from
(select
A.xml.value('(//@node)[1]','nvarchar(20)') as Name, /* XQuery */
A.xml.value('(//word)[1]','nvarchar(225)') as word,
A.xml.value('(//brief)[1]','nvarchar(max)')as brief
from
(Select Convert(xml, xml) AS XML
from [dbo].[cmsContentXml]) AS B
Cross Apply xml.nodes('//items/item') AS A(xml)) D
where ((Name like '%') /*Condition */
OR(word like '%')
OR(brief like '%'))
group by word,Name,brief
order by 3 ASC
Please help with a solution on optimizing or rewriting this query to search for data in the xml column. Also apart from retrieving data from the column.
When in the front end the search button is clicked without entering any keyword the click operation has to retrieve all the items from the database.
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，不允许没有关键字的搜索，而是引导用户使用浏览机制。如果没有什么可搜索的，那么搜索查询的开销就没有意义。
其次，选择 Convert(xml, xml) 是不必要的开销，如果数据库列存储 xml，则应该是 xml。具有 xml 类型的列还允许您在其上放置 xml 索引，这将有助于加快速度。请参阅 http://msdn.microsoft.com/en-us/library/ms191497。有关 XML 索引的更多信息，请访问 http://www.aspx。此外，如果没有转换，您不再需要将其保留为子查询。
第三，不要使用 ORDER BY 3 语法，使用列名。 ORDER BY 3 不是 ANSI 标准，也会导致可维护性问题。
第四，您的 xml 示例数据似乎是无效的 xml（简短的未闭合数据标记，id 没有结束元素，块没有开始元素）。请检查您的数据，如果它确实像真实数据库中那样损坏，则需要修复它，因为错误的 xml 会减慢节点搜索速度。
第五，避免使用单字母别名，这是另一个可维护性问题。
First, don't allow a search with no keyword, direct the user to a browse mechanism instead. There's no point in having the overhead of a search query if there's nothing to search for.
Second, Select Convert(xml, xml) is unnecessary overhead, your database column should be xml if it's storing xml. Having the column with an xml type also allows you to put xml indexes on it which will help speed things up. See http://msdn.microsoft.com/en-us/library/ms191497.aspx for more information on XML Indexes. Also, without the conversion you no longer need to keep that as a subquery.
Third don't use the ORDER BY 3 syntax, use the column name. ORDER BY 3 is not ANSI standard and also can cause maintainability problems.
Fourth, your xml sample data appears to be invalid xml (an unclosed data tag for brief, no closing element for id, no opening element for block). Please check your data, if it's actually broken like that in the real database, it will need to be fixed as bad xml does slow down the node searches.
Fifth, avoid single letter aliases, it's another maintainability issue.