CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008t-sql
DooDoo
DooDoo

Reputation: 13447

How select XML fields node for all rows

I have a table like this :

YEAR    int,
Fields  XML

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

enter image description here

How I can 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

Upvotes: 8

Views: 5138

Answers (1)

marc_s
marc_s

Reputation: 754488

How about this:

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

You're basically selecting Year from the base table and then extracting each <Person> node from the Fields column into an "inline XML table" called E with a single XML column called P (you can choose whatever names you like for those) that you again query and extract the individual elements from .

Upvotes: 11

Related Questions