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

Answers

IN clause on SQL-Column-datatype-XML

darma teja

795

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

Answers (3)

Next Recommended Forum

Trigger Probelm

What is Function in Sql Server

Forum Statistics

Please welcome our newest member prajna varshini.
3,090,019 users have contributed to 147,411 threads and 483,554
In the past 24 hours, we have 3 new threads, 13 new posts, and 85 new users.
In last week, the most popular thread is 'what are libraries are there in python for Schedule'.

Upcoming Events

View all

Get Web3 Ready: Dive into Blockchain with C# Corner
Github Copilot Global Bootcamp - 2025
Vibe Coding Bootcamp