XQuery:查找包含指定 xml 数据的行 [英] XQuery: Finding rows which contain specified xml data

问题描述

我有一个包含许多行的表，每行都有一个名为RecordData"的列，其中包含我希望搜索的 XML 数据.

I have a table which contains many rows, each with a column named "RecordData" that contains XML data that I wish to search upon.

下面给出了来自此列的三行示例 xml 数据:

Three rows worth of example xml data from this column is given below:

1:

<Record>
  <RecordField ID="31" Name="Barcode1" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">ABC123</RecordField>
</Record>

2:

<Record>
  <RecordField ID="15" Name="Field 1" DataTypeId="7" TypeName="Boolean" Decimals="0" Format="" Mandatory="False">true</RecordField>
  <RecordField ID="16" Name="Field 2" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">purpke</RecordField>
</Record>

3:

<Record>
  <RecordField ID="15" Name="Field 1" DataTypeId="7" TypeName="Boolean" Decimals="0" Format="" Mandatory="False">true</RecordField>
  <RecordField ID="16" Name="Field 2" DataTypeId="5" TypeName="String" Decimals="0" Format="" Mandatory="False">12</RecordField>
</Record>

我正在使用以下 SQL 尝试查找任何表行，这些行的 XML 数据包含特定搜索词(在本例中为1").

I am using the following SQL to try and find any table rows, that have XML data that contains a specific search term ('1' in this example).

DECLARE @SearchTerm varchar(max)
    SET @SearchTerm = '1'
    SELECT * 
    FROM MyTableOfData
    WHERE RecordFields.value('contains( (/Record/RecordField/text())[1],sql:variable("@SearchTerm"))','bit') = 1

如您所见，这依赖于出现在第一个RecordField"元素文本中的搜索词，而不是搜索所有RecordField"节点.意思是，我得到的唯一结果是第 1 行，而不是第 1 行和第 3 行.

As you will see, this relies on the search term appearing in the first "RecordField" element's text, rather than searching over ALL of the "RecordField" nodes. Meaning, the only result I get back would be row 1, rather than both rows 1 and 3.

我已经阅读了一些相关的 MSDN 文章，并且在 Google 失败了一天，因为我没有更接近于找出如何消除限制并最终破解它.

I've done a bit of reading through the related MSDN articles, and am having a Google-fail day, as I'm not getting any closer to finding out how to remove the limitation and finally crack this.

任何帮助将不胜感激:)

Any help would be greatly appreciated :)

DECLARE @SearchTerm varchar(max)
SET @SearchTerm = '1'
select *
from MyTableOfData
    cross apply MyTableOfData.RecordFields.nodes('/Record/RecordField') as tx(r)
where  tx.r.value('.','varchar(10)') like '%'+@searchterm+'%'

抛出:

Msg 493, Level 16, State 1, Line 3
The column 'r' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

编辑 2:

当你准确地复制你被告知的内容时，而不是遗漏东西，这很有效！:

And when you copy exactly what you're told, instead of missing things, this works!:

DECLARE @SearchTerm varchar(max)
SET @SearchTerm = '1'
select MyTableOfData.*
from MyTableOfData
    cross apply MyTableOfData.RecordFields.nodes('/Record/RecordField') as tx(r)
where  tx.r.value('.','varchar(10)') like '%'+@searchterm+'%'

推荐答案

CROSS APPLY 是你没找到的...

select yourtable.*
from yourtable
    cross apply yourtable.RecordFields.nodes('/Record/RecordField') as tx(r)
where  tx.r.value('.','varchar(10)') like '%'+@searchterm+'%'

或

where tx.r.value('contains((.),sql:variable("@searchterm"))','bit')=1

这篇关于XQuery:查找包含指定 xml 数据的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！