SQL Server 2017导入XML数据查询返回多行如何合并为单条结果

数据老陈

2026-9-25

问题原因

你当前查询返回3行的核心原因是nodes()方法同时匹配了SettlementFile/Transaction下的3个不同子节点（Identifier、TransactionInfo、CustomerInput），每匹配到一个节点就会单独生成一行，所以同一条交易会返回3条结果。

修正方案

调整nodes()的匹配路径到交易根节点SettlementFile/Transaction，取值时指定完整的节点层级即可，每个交易只会生成一行结果。同时可以省略冗余的query()调用，直接用value()方法指定路径取值，执行效率更高：

SELECT 
    MY_XML.Transaction.value('(Identifier/StationID/text())[1]', 'VARCHAR(50)') AS StationID,
    MY_XML.Transaction.value('(Identifier/TransactionTimestamp/text())[1]', 'DATETIME') AS TransactionTimestamp,
    MY_XML.Transaction.value('(TransactionInfo/TicketNumber/text())[1]', 'INT') AS TicketNumber,
    MY_XML.Transaction.value('(TransactionInfo/CustomerInput/Mileage/text())[1]', 'VARCHAR(50)') AS Mileage
FROM
    (
        SELECT CAST(MY_XML AS XML) AS XML_CONTENT
        FROM OPENROWSET(BULK '\\EO-TEST\SQL-Daten\Temp\MY_XML.xml', SINGLE_BLOB) AS T(MY_XML)
    ) AS T
CROSS APPLY XML_CONTENT.nodes('SettlementFile/Transaction') AS MY_XML(Transaction);