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

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

```