SQL select查询根据xml节点值获取记录？

I have a column of ntext data type and NOT XML. It stores all xml data. I need to get records based on xml node value. =>input value is CpsiaId = 456 and should return all records which has this value in the xml

我有一列ntext数据类型和非XML。它存储所有xml数据。我需要根据xml节点值获取记录。 =>输入值为CpsiaId = 456，并且应该返回在xml中具有此值的所有记录

I tried select * from tableName where convert(xml,column_name).value('data((/root/ProductInformation/CPSIA/CpsiaDetails/Item/CpsiaId)[1])','int') = 456

我试过select * from tableName where convert（xml，column_name）.value（'data（（/ root / ProductInformation / CPSIA / CpsiaDetails / Item / CpsiaId）[1]）'，'int'）= 456

but it didn't work....any ideas or other way of getting the records based xml node value.

但它没有工作....任何想法或其他方式获取基于xml节点值的记录。

Sample Xml:

示例Xml：

<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>

2 个解决方案

#1

convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=456]') = 1

This works, and you can replace the constant (456) with an sql:variable() if you need a dynamic value, like so (assuming a numeric variable @i):

如果需要动态值，可以用sql：variable（）替换常量（456），如下所示（假设数字变量为@i）：

'/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]'

Edit:

编辑：

Sample code as requested:

请求的示例代码：

DECLARE @t nvarchar(MAX);
SET @t = '<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'

DECLARE @i int;
SET @i = 456;

SELECT convert(xml,@t).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]')

#2

It may be easier to use a CTE to convert it to XML first and then do a CROSS Apply. Here's a sample

使用CTE首先将其转换为XML然后执行CROSS应用可能更容易。这是一个样本

Declare @test table (id int identity, xmlData nvarchar(max))
Insert Into @test 
(xmldata)
Values 
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>'),
('<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>999</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>123</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>')

;with cte as (select id, convert(xml,t.xmlData) xdata from @test t )
SELECT t.*
     from cte inner join @test t
     on cte.id = t.id
    cross apply cte.xdata.nodes('//root/ProductInformation/CPSIA/CpsiaDetails/Item') x(nd)
WHERE
    x.nd.value('CpsiaId[1]','int') =456

Using Lucero's answer for the Select against a table (rather than an xml variable)

使用Lucero对表的选择（而不是xml变量）的答案

DECLARE @i int;
SET @i = 456;
SELECT * 
FROM 
    @test 
WHERE
    convert(xml,xmlData).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@i")]') = 1

#1

convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=456]') = 1