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

2018-06-30 21:55:15

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 ?

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

链接地址: http://www.djcxy.com/p/86224.html