CODEWITHSUNDEEP
sqlxml
Pratik Sawant
Pratik Sawant

Reputation: 35

read xml data in sql for each rows

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;

Upvotes: 2

Views: 151

Answers (1)

xanatos
xanatos

Reputation: 111940

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.

Upvotes: 1

Related Questions