How to run a query where it displays all items of a XML node?

Question

<DataItem name="xxxxx" value="2" />
<Roles>
  <Role id="Role1" />
  <Role id="Role2" />
  <Role id="Role3" />
  <Role id="Role4" />
</Roles>

This XML is in table A, column Z.

How would I go about writing a query to see all roles in this XML?

Example:

SELECT Z FROM A

Result:

Role1
Role2
Role3   
Role4

I also need to take into account that the roles are DYNAMIC ... I never know how many there will be in there.

Answer 1

select T.N.value('@id', 'varchar(10)')
from A
  cross apply Z.nodes('/Roles/Role') as T(N)

xml Data Types Methods