How to covert an xml values to a table in SQL server 2005?

The below code will read xml data from a variable and will covert it to a temporary table.


declare @InputTable xml set @InputTable ='<emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="12009 " InvAmount="6985.00" SettledAmt="0.00" BalanceAmt="6985.00" Age="304" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="12425 " InvAmount="36.00" SettledAmt="0.00" BalanceAmt="36.00" Age="297" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="14723 " InvAmount="669.00" SettledAmt="0.00" BalanceAmt="669.00" Age="261" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="15353 " InvAmount="140.00" SettledAmt="0.00" BalanceAmt="140.00" Age="249" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" />' Declare @TempTable table ( CustomerId int, DepartmentID int, DepartmentName varchar(50), InvocieId nchar(20), InvAmount numeric(18,2), SettledAmt numeric(18,2), BalanceAmt numeric(18,2), Age int, Due char(10), Processed bit, CurrencyName nvarchar(40), Symbol nvarchar(40), Invoicedate datetime, Invoiceno varchar(50), Referenceno varchar(100), remarks varchar(1000) ) insert into @TempTable SELECT Tbl.Col.value('@CustomerId', 'INT'), Tbl.Col.value('@DepartmentID', 'INT'), Tbl.Col.value('@DepartmentName', 'varchar(50)'), Tbl.Col.value('@InvocieId', 'nchar(20)'), Tbl.Col.value('@InvAmount', 'numeric(18,2)'), Tbl.Col.value('@SettledAmt', 'numeric(18,2)'), Tbl.Col.value('@BalanceAmt', 'numeric(18,2)'), Tbl.Col.value('@Age', 'INT'), Tbl.Col.value('@Due', 'char(10)'), Tbl.Col.value('@Processed', 'bit'), Tbl.Col.value('@CurrencyName', 'nvarchar(40)'), Tbl.Col.value('@Symbol', 'nvarchar(40)'), Tbl.Col.value('@Invoicedate', 'datetime'), Tbl.Col.value('@Invoiceno', 'varchar(50)'), Tbl.Col.value('@Referenceno', 'varchar(100)'), Tbl.Col.value('@remarks', 'varchar(1000)') FROM @InputTable.nodes('//EmailList') Tbl(Col) order by Tbl.Col.value('@Age','INT') select * from @TempTable Regards Rajesh Kamalakshan