How to select XML node fields for all rows

Question

I have a table like this:

YEAR int, Fields XML 

My XML column has this structure for all rows, but with different meanings:

How can I get this result:

 YEAR ID NAME LASTNAME --------------------------------------------------- 2011 1000 Nima Agha 2011 1001 Begha Begha 2011 1002 Jigha Jigha 2011 1003 Aba Aba 2012 1034 AAA BBB ... 

thanks

Answer 1

How about this:

  SELECT Year, EPvalue('(ID)[1]', 'INT') AS 'ID', EPvalue('(Name)[1]', 'VARCHAR(50)') AS 'Name', EPvalue('(LastName)[1]', 'VARCHAR(50)') AS 'LastName' FROM dbo.YourTable CROSS APPLY Fields.nodes('/Employees/Person') AS E(P) 

You basically select Year from the base table and then retrieve each <Person> node from the Fields column into an "embedded XML table" called E with one XML column called P (you can choose any names you like) that you again Query and retrieve from individual items.