Get the columns from the XML data

Question

I have table with following columns

 

tblProductProperties

(ID {Bigint},

Name {Varchar},

AccountID {long},

FileDate {DateTime},

RawXMLData {xml}

) 

 

Now raw xml is contains data as follows

<R01>

<RAW_CODE />

<CORRESPONDENCE_ID>Some Int values</CORRESPONDENCE_ID>

<FIRST_NAME>Name1</FIRST_NAME>

<LAST_NAME>Name2</LAST_NAME>

<COUNTY />

<EMAIL>[email protected]</EMAIL>

<ACCTID>Some Int value </ACCTID>

<COLOR>Text value</COLOR>

<WEIGHT>Weight description </WEIGHT>

</R01>

 

select  * tblProductProperties 

out put of query looks like as follow  

 

 

 ID   Name AccountID    FileDate         RawXMLData

101  ABC   123133         2014/11/12   " <R01> <RAW_CODE /> <CORRESPONDENCE_ID>1293</CORRESPONDENCE_ID> <FIRST_NAME>Joan</FIRST_NAME> <LAST_NAME>Wade</LAST_NAME>  <COUNTY /> <EMAIL>[email protected]</EMAIL> <ACCTID>93</ACCTID> <COLOR>Red </COLOR> <WEIGHT>60GMS</WEIGHT> </R01>"

 

102 AVC 120201 2014/11/12 " <R01> <RAW_CODE /> <CORRESPONDENCE_ID>1292</CORRESPONDENCE_ID> <FIRST_NAME>Name1</FIRST_NAME> <LAST_NAME>Name2</LAST_NAME>  <COUNTY /> <EMAIL>[email protected]</EMAIL> <ACCTID>92</ACCTID> <COLOR>Blue </COLOR> <WEIGHT>60GMS</WEIGHT> </R01>"

 

Now i want to show output as follows

 

ID    Name AccountID FileDate      RAW_CODE  CORRESPONDENCE_ID FIRST_NAME LAST_NAME COUNTY EMAIL            ACCTID COLOR WEIGHT

 101  ABC   123133     2014/11/12  NULL               1293                                 Joan              Wade           Null        joan2054@   93           RED    60gms

                                                                                                                                                                                    yahoo.com

 102 AVC    120201     2014/11/12 NULL                1292                                 Name1         Name2         NULL     name1@            92        Blue  60gms

                                                                                                                                                                                       yahoo.com

 

 

Can some one help me to find out how to solve this.