sql - 遍历 SQL Server 中的 XML 列

Question

我想遍历 XML 列并生成以下报告

表 EmpTransaction 结构

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

XML结构如下

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

要求的输出

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

我曾尝试使用 tempTable 实现上述报告需要单个查询才能获得上述报告

我尝试使用以下解决方法实现

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

需要知道上面的查询可以简化而不是创建临时表

Answer 1

您可以通过简单的交叉应用来完成。

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;