CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
Nilesh Gajare
Nilesh Gajare

Reputation: 6398

Iterate Through XML Column in SQL Server

I want to iterate through XML Column and generate below Report

Table EmpTransaction Structure

EmployeeId       TransactionFieldDetails
458               <PayDetails>...</PayDetails>
459               <PayDetails>...</PayDetails>

Below is the XML Structure

<PayDetails>
  <Column Name="NETPAY" DataType="float" Value="45112" />
  <Column Name="TDS" DataType="float" Value="150000" />
</PayDetails>

Required Output

EmployeeId     FieldName    Value
 458              NETPAY    45112
 458              TDS       15000
 459              NETPAY    45236
 459              TDS       17000

I had tried to achieve above report using tempTable Require single query to achive above report

I tried to achieve using below Workaround

create table #EMPXML (employeeId int, fldname varchar(max),fldval varchar(max))
select ROW_NUMBER()OVER(ORDER BY EmployeeId )AS ID ,EmployeeId,TransactionFieldDetails into #MKISQS_XML from dbo.EmpTrans 

DECLARE @TOTINQ INT = (select COUNT(DISTINCT EmployeeId) from #MKISQS_XML)
DECLARE @INQCNT INT = 1
DECLARE @INQCODE VARCHAR(10)
DECLARE @INQXML XML
DECLARE @RELATEDTO VARCHAR(15)
WHILE(@TOTINQ >=@INQCNT)
BEGIN
    SET @INQCODE = (SELECT EmployeeId FROM #MKISQS_XML WHERE ID=@INQCNT)
    SET @INQXML  = (SELECT TransactionFieldDetails FROM #MKISQS_XML WHERE ID=@INQCNT)

    INSERT INTO #EMPXML 
    SELECT @INQCODE,            
             T.c.value('(@Name)[1]', 'Varchar(max)') AS fldname,
             T.c.value('(@Value)[1]', 'Varchar(max)') AS fldval
    FROM @INQXML.nodes('//PayDetails/Column') T(c)
    SET @INQCNT = @INQCNT+1
END

select * from #EMPXML

Need to know can above query can be simplify instead of creating temp table

Upvotes: 3

Views: 3111

Answers (1)

mxix
mxix

Reputation: 3659

You can do it with a simple cross apply.

CREATE TABLE #tXML (
    EmployeeId int,
    TransactionFieldDetails XML
);

insert into #tXML 
(EmployeeId, TransactionFieldDetails)
values
(458,'<PayDetails><Column Name="NETPAY" DataType="float" Value="45112" /><Column Name="TDS" DataType="float" Value="150000" /></PayDetails>'),
(459,'<PayDetails><Column Name="NETPAY" DataType="float" Value="45236" /><Column Name="TDS" DataType="float" Value="17000" /></PayDetails>');


select
    T.EmployeeId,
    X.[FieldName],
    X.[Value]
from #tXML T
cross apply (
SELECT         
            T.c.value('@Name', 'Varchar(max)') AS [FieldName],
            T.c.value('@Value', 'Varchar(max)') AS [Value]
FROM TransactionFieldDetails.nodes('/PayDetails/Column') T (c)
) X;

DROP TABLE #tXML;

Upvotes: 4

Related Questions