Nilesh Nilesh - 6 months ago 10
SQL Question

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

Answer

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;
Comments