SQL select query to get records based on xml node value?

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

I tried select * from tableName whe开发者_运维技巧re 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.

Sample 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>

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):

'/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")]')

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

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)

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