read xml data in sql for each rows

Question

This is my table

id          paydetails
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           <PayDetails><Column Name="NETPAY" DataType="VARCHAR(3000)" Value="40710.00" /><Column Name="EPFAMOUNT" DataType="VARCHAR(3000)" Value="4499.00" /><Column Name="FPFAMOUNT" DataType="VARCHAR(3000)" Value="541.00" /><Column Name="GROSS" DataType="VARCHAR(3000
2           <PayDetails><Column Name="NETPAY" DataType="VARCHAR(3000)" Value="49210.00" /><Column Name="EPFAMOUNT" DataType="VARCHAR(3000)" Value="5549.00" /><Column Name="FPFAMOUNT" DataType="VARCHAR(3000)" Value="541.00" /><Column Name="GROSS" DataType="VARCHAR(3000

required output:-

id  NETPAY      EPFAMOUNT   FPFAMOUNT   GROSS       PTGROSS
1    40710.00   4499.00     541.00      47200.00    42000.00
2    49210.00   5549.00     541.00      58250.00    50750.00

I have tried following code, but problem is that by default it is returning only last row. Because I am passing xml as a parameter, I want result for each record.

declare @xml xml
select @xml = paydetails
from empPayDetails 

declare @SQL nvarchar(max) = 'select '+ 
  (
  select ',T.X.value(''Column[@Name = "'+T.ColName+'"][1]/@Value'','''+T.DataType+''') as '+quotename(T.ColName)
  from (
       select T.X.value('@Name', 'nvarchar(128)') as ColName,
              T.X.value('@DataType', 'nvarchar(128)') as DataType
       from @XML.nodes('/PayDetails/Column') as T(X)) as T        
  for xml path(''), type
  ).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+
'from @XML.nodes(''/PayDetails'') as T(X)';

print @sql

exec sp_executesql @SQL, N'@XML xml', @XML;

xanatos · Accepted Answer · 2015-03-04 10:33:58Z

1

The query should be:

declare @SQL nvarchar(max) = 'select '+ 
  (
  select ',T.X.value(''Column[@Name = "'+T.ColName+'"][1]/@Value'','''+T.DataType+''') as '+quotename(T.ColName)
  from (
       select distinct T.X.value('@Name', 'nvarchar(128)') as ColName,
              T.X.value('@DataType', 'nvarchar(128)') as DataType
       from empPayDetails 
       cross apply paydetails.nodes('/PayDetails/Column') as T(X)) as T        
       for xml path(''), type
  ).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+
'from empPayDetails cross apply paydetails.nodes(''/PayDetails'') as T(X)';

print @sql

exec sp_executesql @SQL;

Note the use of cross apply to use an xml column of a table, the use of distinct (otherwise you'll have the same columns repeated), the fact that I removed the parameter of the exec.

edited Mar 4, 2015 at 10:33

answered Mar 4, 2015 at 10:25

xanatos

112k13 gold badges209 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

read xml data in sql for each rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related