如何在SQL中将嵌套XML列转换为表格格式？

安全阿岳

2026-7-11

拆解XML列至关系型表格的T-SQL实现

DDL及示例数据

-- DDL及示例数据初始化
DECLARE @tbl TABLE (OwnerID INT IDENTITY PRIMARY KEY, House XML);

INSERT @tbl (House) 
VALUES (N'<House>
              <Room id="111111" type="b" name="Master Bedroom" formatstring="">
                  <Closest>3</Closest>
                  <Windows>4</Windows>
              </Room>
              <Room id="222222" type="a" name="Guest Bedroom" formatstring="">
                  <Closest>1</Closest>
                  <Windows>2</Windows>
              </Room>
              <Room id="333333" type="a" name="Bathroom" formatstring="">
                  <Closest>0</Closest>
                  <Windows>2</Windows>
              </Room>
              <Room id="444414" type="b" name="Kitchen" formatstring="">
                  <Closest>1</Closest>
                  <Windows>0</Windows>
              </Room>
         </House>');

待完善的尝试代码

...

期望输出

OwnerID	id	type	name	Closest	Windows
1	111111	b	Master Bedroom	3	4
1	222222	a	Guest Bedroom	1	2
1	333333	a	Bathroom	0	2
1	444414	b	Kitchen	1	0

SQL Server版本

执行SELECT @@VERSION;返回：

Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64)   
May 26 2023 12:52:08   
Copyright (C) 2022 Microsoft Corporation  
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )

实现代码

利用SQL Server的XML原生支持，通过nodes()方法拆分XML节点，value()提取属性与子节点值：

SELECT 
    t.OwnerID,
    room.value('@id', 'VARCHAR(20)') AS id,
    room.value('@type', 'CHAR(1)') AS type,
    room.value('@name', 'VARCHAR(50)') AS name,
    room.value('@formatstring', 'VARCHAR(50)') AS formatstring,
    room.value('(Closest/text())[1]', 'INT') AS Closest,
    room.value('(Windows/text())[1]', 'INT') AS Windows
FROM @tbl t
CROSS APPLY t.House.nodes('/House/Room') AS Rooms(room);