在 SQL 中将带有嵌套 XML 的列转换为表格格式？-解网

问：

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

...

期望输出：

所有者 ID	编号	类型	名字	最近	窗户
1	111111	b	主卧室	3	4
1	222222	一个	客房	1	2
1	333333	一个	浴室	0	2
1	444414	b	厨房	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 服务器 xml

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和样本数据填充，即 CREATE 表加上 INSERT T-SQL 语句。（2）你需要做什么，即逻辑和你的代码尝试在T-SQL中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。

1赞 Yitzhak Khabinsky 9/8/2023

此外，提供的 XML 格式不正确。

0赞 Dale K 9/8/2023

请告知您尝试过什么以及卡住的地方。

0赞 Adrian Maxwell 9/8/2023

什么是“最接近”？duckduckgo.com/?q=define%3AClosest&ia=definition它应该是壁橱吗？

1赞 Yitzhak Khabinsky 9/8/2023

我编辑了您的问题，并将其转换为最小的可重复示例。您现在只需要提供 #2。

答：

0赞 marc_s 9/8/2023 #1

您可以尝试如下操作：

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)

这应该会为您返回所需的数据。

您基本上是将 XML “切碎”为根节点下每个元素的 XML 片段列表，然后访问该元素并挑选出各种属性值（等），以及访问一些子元素并获取它们的值<Room><House><Room>idtype

更新

如果你想支持多个元素和子元素 - 试试这样的方法：<Closet><Windows>

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)

在 SQL 中将带有嵌套 XML 的列转换为表格格式？

Convert a column with nested XML into tabular format in SQL?

评论

评论