将具有嵌套 XML 的列转换为 SQL 中的表格格式

发布于09月08日

DDL和示例数据:

-- DDL and sample data population, start
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>');
-- DDL and sample data population, end

下面是我通过T-SQL将House列分解为单独列的try :

...

请注意，情况并非如此.

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: )

SELECT OwnerID, RoomId = xc.value('@id', 'int'), RoomType = xc.value('@type', 'varchar(50)'), RoomName = xc.value('@name', 'varchar(50)'), FormatString = xc.value('@formatstring', 'varchar(50)'), Closets = xc.value('(Closest/text())[1]', 'int'), Windows = xc.value('(Windows/text())[1]', 'int') FROM @tbl CROSS APPLY House.nodes('/House/Room') AS XT(XC)

SELECT OwnerID, RoomId = xc.value('@id', 'int'), RoomType = xc.value('@type', 'varchar(50)'), RoomName = xc.value('@name', 'varchar(50)'), FormatString = xc.value('@formatstring', 'varchar(50)'), Closets = xc2.value('(./text())[1]', 'int'), Windows = xc3.value('(./text())[1]', 'int') FROM @tbl CROSS APPLY House.nodes('/House/Room') AS XT(XC) CROSS APPLY XC.nodes('Closet') AS XT2(XC2) CROSS APPLY XC.nodes('Windows') AS XT3(XC3)

将具有嵌套 XML 的列转换为 SQL 中的表格格式

推荐答案

Sql相关问答推荐

获取家谱树中第一次出现的特定信息，然后停止

SQL—如何在搜索的元素之后和之前获取元素？

JSON列之间的Postgr聚合

在多个柱上连接时，如何确定连接条件？

如何查找所提供日期范围的所有季度开始日期和结束日期

将日期时间转换为日期格式

每小时 Select 1行

显示十进制列，但尽可能显示为整数

如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

从输出中删除 jsonb_build_object

在特定条件下使用 LAG，确定要采用什么 LAG 值？

Postgresql 生成器列导致语法错误

Clickhouse：左连接表到外部数组

Postgres存在限制问题「小值」

在没有订单的情况下，如何生成一个值为0的顾客天数行

一次 Select 语句中按组累计的SQL累计数

自动生成计算频率的列

如何获得上个月和下个月之间的销售额差异

如何从 2 个 SQLite 表构建嵌套对象？

连接表时避免重复