xml - SQL : How to find rows by filtering on a value within an XML column

Question

我有一个具有 XML 数据类型的字段，下面是我保存在该字段中的 XML 数据示例。现在，我想选择 <SiteId> 中包含“1”的数据使用下面的查询标记，但没有结果出来。

SELECT [SettingsID]
      ,[XMLData]
FROM Settings
Where [XMLData].exist('/Settings/SiteId/text()[contains(.,"1")]') =1 


XMLDATA sample

<Settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SiteId>1</SiteId>
  <ModuleId xsi:nil="true" />
  <TabId xsi:nil="true" />
  <Version>0</Version>
</Settings>

有人对此有想法吗？

Answer 1

你的问题的标题是 - 大概 - 误导......

我认为您不是在寻找全文搜索，而是在寻找合适的 XQuery查找具有给定条件的条目。

试试这个:

DECLARE @mockupTable TABLE(SettingsID INT IDENTITY, XMLData XML);
INSERT INTO @mockupTable VALUES
 (N'<Settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SiteId>1</SiteId>
  <ModuleId xsi:nil="true" />
  <TabId xsi:nil="true" />
  <Version>0</Version>
</Settings>')
,(N'<Settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SiteId>2</SiteId>
  <ModuleId xsi:nil="true" />
  <TabId xsi:nil="true" />
  <Version>0</Version>
</Settings>');

--查询测试，如果有 <SiteId> ，其 text()是 1

SELECT[SettingsID]
      ,[XMLData]
FROM @mockupTable
Where [XMLData].exist('/Settings/SiteId[text()="1"]') =1;

您可以介绍 1只需使用 sql:variable("@SomeIntVariable") 即可查询而不是 "1"

DECLARE @id INT=2;
SELECT[SettingsID]
      ,[XMLData]
FROM @mockupTable
Where [XMLData].exist('/Settings/SiteId[text()=sql:variable("@id")]') =1 

更新

你可以使用 尽可能地创建它。深度搜索 与 //和 *:省略任何命名空间:

SELECT[SettingsID]
      ,[XMLData]
FROM @mockupTable
Where [XMLData].exist('//*:SiteId[text()="1"]') =1;