Querying a table that contains an XML column

Question

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

Where do you to find `%something%`? In an element value? An element name? An attribute name? — Anon, Aug 05 '14 at 14:45
something is a string , but CAST(Value AS VARCHAR(MAX)) worked for me — Mehdi Souregi, Aug 05 '14 at 14:46

score 2 · Accepted Answer · answered Aug 05 '14 at 14:43

2

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%'

answered Aug 05 '14 at 14:43

How 'bout a Fresca

2,267
1
15
26

Great ! Worked like a charm , txs :) – Mehdi Souregi Aug 05 '14 at 14:45

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

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

edited May 23 '17 at 10:32

Community

1
1

answered Aug 05 '14 at 15:20

singhswat

832
7
20

Querying a table that contains an XML column

2 Answers2