CODEWITHSUNDEEP
sqlsql-server
Mehdi Souregi
Mehdi Souregi

Reputation: 3265

Querying a table that contains an XML column

I have a table that contains an xml column, my table looks like the following:

MyTable
    Id(Pk, int,not null)
    Name(varchar(50), not null)
    Value(XML(.), not null)

The type of Value is XML

I've tried the following query and of course it is not working

/****** Script ******/
SELECT TOP 1000 [Id]
      ,[Name]
      ,[Value]
  FROM [Value]
  where Value like '%something%'

How can I get columns that contains something in their xml value

Upvotes: 1

Views: 244

Answers (2)

singhswat
singhswat

Reputation: 906

XML

<root>
<role>Alpha</role>
<role>Beta</role>
<role>Gamma</role>
</root>

select  [Name]  ,[Value]
from  Value
where  Value.value('(/root/role)[1]', 'varchar(max)') like '%Beta%'

also refer How can I query a value in SQL Server XML column

Upvotes: 0

How &#39;bout a Fresca
How &#39;bout a Fresca

Reputation: 2317

Would this work? I'm assuming you meant to select FROM MyTable, not from [Value]:

SELECT TOP 1000 [Id]
    ,[Name]
    ,[Value]
FROM [MyTable]
where CAST(Value AS VARCHAR(MAX)) like '%something%'

Upvotes: 2

Related Questions