I have an XML text in the column(e.g column name: xmlColumn in Customer tabel) I am searching based on xpath (Address/street/No) and using IN operator. Select * from tbCustomer where xmlColumn (Address/street/ No ) IN (1, 2, 3) How can I achieve this? Thanks Darma

Hi Darman, you should try query as below where it_xml_Template.exist( 'Address/street/No/text()=1001' ) = 1

Here you can use like query to fetch data from xml fields in 2 ways SELECT * FROM tbCustomer WHERE CAST (xmlColumn AS NVARCHAR( MAX )) like (1) OR CAST(xml AS NVARCHAR(MAX)) like ('2') or the other way to find using xml is SELECT * FROM tbCustomer WHERE xmlColumn.exist( 'fields/field[@id="Address" and text()=1]' ) = 1 OR xmlColumn.exist( 'fields/field[@id="Street" and text()=2]' ) = 1 OR xmlColumn.exist( 'fields/field[@id="No" and text()=3]' ) = 1

Thanks @Priyanka Jain The first query works fine. But I wanted to use the second query, but it is not working. Here is the query I wrote: No has number 1001 where it_xml_Template.exist( 'Address/street/No/text()' ) = 1001 It return the empty rows. But I have on row in table