Select distinct list of all elements x of all records in sql xquery

sql, xml, xquery

Solution

select distinct T.N.value('.','nvarchar(64)')
from table1
  cross apply xmlCol.nodes('//interest/@id') as T(N)

Problem

Below query expression: ``` SELECT distinct xmlCol.value('(//interest/@id)[1]','nvarchar(64)') FROM table1 ``` Returns a list of id of first interest element of all records. But an xml may contains multiple interest elements. So, how to get a distinct list of all interest elements of all records in sql xquery ?

Original source