Cool One.!!

Another way to fetch the XML Element without loop will be by using the XQuery function Text.

For same xml this will work.

SELECT T.c.value('(Name/text())[1]', 'varchar(256)') as Name,T.c.value('(Department/text())[1]', 'varchar(256)') as Department

FROM...