declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'Beta'

select Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

If you want the query to work as where col like '%Beta%' you can use contains

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'et'

select Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

Considering that XML data comes from a table 'table' and is stored in a column 'field': use the XML methods, extract values with xml.value(), project nodes with xml.nodes(), use CROSS APPLY to join:

SELECT 
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
    CROSS APPLY field.nodes('/person') t(p)

You can ditch the nodes() and cross apply if each field contains exactly one element 'person'. If the XML is a variable you select FROM @variable.nodes(...) and you don't need the cross apply.

I've been trying to do something very similar but not using the nodes. However, my xml structure is a little different.

You have it like this:

<Metrics>
    <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>

If it were like this instead:

<Metrics>
    <Metric>
        <id>TransactionCleanupThread.RefundOldTrans</id>
        <type>timer</type>
        .
        .
        .

Then you could simply use this SQL statement.

SELECT
    Sqm.SqmId,
    Data.value('(/Sqm/Metrics/Metric/id)[1]', 'varchar(max)') as id,
    Data.value('(/Sqm/Metrics/Metric/type)[1]', 'varchar(max)') AS type,
    Data.value('(/Sqm/Metrics/Metric/unit)[1]', 'varchar(max)') AS unit,
    Data.value('(/Sqm/Metrics/Metric/sum)[1]', 'varchar(max)') AS sum,
    Data.value('(/Sqm/Metrics/Metric/count)[1]', 'varchar(max)') AS count,
    Data.value('(/Sqm/Metrics/Metric/minValue)[1]', 'varchar(max)') AS minValue,
    Data.value('(/Sqm/Metrics/Metric/maxValue)[1]', 'varchar(max)') AS maxValue,
    Data.value('(/Sqm/Metrics/Metric/stdDeviation)[1]', 'varchar(max)') AS stdDeviation,
FROM Sqm

To me this is much less confusing than using the outer apply or cross apply.

I hope this helps someone else looking for a simpler solution!

This should work:

DECLARE @xml XML

SET @xml = N'<email><account language="en" /></email>'

SELECT T.C.value('@language', 'nvarchar(100)')
FROM @xml.nodes('email/account') T(C)

Hi @Andrey Piatov
If you want the id value display in one row, then please check this sample:

DECLARE @tbl TABLE (Xml_ID INT IDENTITY PRIMARY KEY, xmldata XML);  
INSERT INTO @tbl (xmldata) VALUES  
 (N'853503512511'),  
 (N'853555544533522'),  
 (N'853511566'),  
 (N'853');  
  
--Use STRING_AGG (Need SQL Server 2017 (14.x) and later)  
;WITH CTE AS  
(  
 SELECT Xml_ID,n.x.value('id[1]','INT') AS Executors_Value  
 FROM @tbl t  
 OUTER APPLY xmldata.nodes('/data/user[@name="Executors"]/value') n(x) --Use CROSS APPLY instead of OUTER APPLY if you do not want NULL Executors_Value  
)  
SELECT Xml_ID,STRING_AGG(Executors_Value, ',') WITHIN GROUP (ORDER BY Executors_Value) AS Executors_Value  
FROM CTE  
GROUP BY Xml_ID  
  
--Try this query if not support STRING_AGG  
;WITH CTE AS  
(  
 SELECT Xml_ID,n.x.value('id[1]','INT') AS Executors_Value  
 FROM @tbl t  
 OUTER APPLY xmldata.nodes('/data/user[@name="Executors"]/value') n(x)  
)  
SELECT Xml_ID,  
       STUFF((SELECT ',' + CAST(Executors_Value AS VARCHAR) FROM CTE WHERE Xml_ID=T.Xml_ID FOR XML PATH('')),1,1,'')AS Executors_Value  
FROM CTE T  
GROUP BY Xml_ID  

Output:

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.