个人中心
gpt4 book ai didi

Convert a column with nested XML into tabular format in SQL?(是否将嵌套了XML的列转换为SQL中的表格格式？)

转载 作者：bug小助手 更新时间：2023-10-24 21:26:49 27 4
gpt4 key购买 nike



DDL and sample data:

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

Here is my attempt to shred the House column into individual columns via T-SQL:

以下是我通过T-SQL将House专栏分解为单独的专栏的尝试：


...

Desired output:

所需输出：





















































OwnerID id type name formatstring 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 version: SELECT @@VERSION; emits the following:

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

更多回答

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;).

在提问时，您需要提供一个最小的可重复性示例：(1)DDL和样本数据填充，即CREATE TABLE(S)加上INSERT T-SQL语句。(2)您需要做什么，即逻辑和您的代码尝试在T-SQL中实现。(3)期望输出，基于上述#1中的样本数据。(4)您的SQL Server版本(选择@@Version；)。

Also, the provided XML is not well-formed.

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

Please advise what you have attempted and where you got stuck.

请告知您曾尝试过什么以及您在哪里被卡住了。

What is a "Closest"? duckduckgo.com/?q=define%3AClosest&ia=definition Should it be closet?

什么是“最近的”？Duckduckgo.com/?q=define%3AClosest&ia=definition应该把它放在壁橱里吗？

I edited your question, and converted it into a minimal reproducible example. You just need to provide #2 now.

我编辑了你的问题，并将其转换为一个最小的可重现的例子。你现在只需要提供2号。

优秀答案推荐

You can try something like this:

您可以尝试这样的操作：


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)

That should return the desired data for you.

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


You're basically "shredding" the XML into a list of XML fragments for each <Room> element under the <House> root node, and then accessing that <Room> elements and picking out the various attribute values (id, type etc.), as well as accessing some of the subelements and getting their values

您基本上是将XML“分解”到 根节点下每个 元素的一列XML片段中，然后访问该 元素并选择各种属性值(id、type等)，以及访问一些子元素并获取它们的值


Update

更新


If you want to support multiple <Closet> as well as <Windows> subelements - try something like this:

如果您希望支持多个 和 子元素，请尝试如下所示： 


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)

更多回答

This Worked! Thank you.

这招奏效了！谢谢。

I’m encountering another issue where it’s possible to have multiple similar XML elements. Ie: 2 or more <closet> within the same <Room>. However, the current code does not account for that. Can you advise?

我遇到了另一个问题，其中可能有多个类似的XML元素。即：在同一个<房间>内有两个或更多<房间>。然而，当前的代码没有考虑到这一点。你能给点建议吗？

@Xoom: well, that's a whole new question, really - but basically, you'll need a second level of CROSS APPLY to handle multiple subelements in a <Room> XML element ....

@Xoom：嗯，这是一个全新的问题，真的--但基本上，您需要第二个级别的交叉应用来处理XML元素中的多个子元素……

This works gloriously when I added 1 additional CROSS APPLY for “closest” only. However, this progress is lost when I try to apply a second CROSS APPLY for “windows” for a total of 3 in my query. Using the same logic, I get an error. Can you advise how 3 cross apply can successfully be implemented?

当我添加了一个额外的十字架时，这个效果非常好，只适用于最近的。然而，当我试图在查询中为总共3个窗口应用第二个交叉应用时，这一进展就丢失了。使用相同的逻辑，我得到一个错误。你能告诉我如何才能成功地实施三交叉应用吗？

Thank you, sir. As a new user, they won’t let me upvote your post until I get 15 credit scores. I was able to replicate this using “outer apply” to get all the null values. Thank you!

谢谢您，先生。作为一名新用户，他们不会让我给你的帖子加分，直到我得到15分信用评分。我可以使用“外部应用”来复制它，以获得所有的空值。谢谢!

27 4 0
文章推荐： Side brackets next to a vector in Typst(Typst中向量旁边的方括号)
文章推荐： ERROR: Package 'dsinternals' requires a different Python: 2.7.18 not in '>=3.4'(错误：包‘dsinterals’需要不同的Python：2.7.18，不在‘>=3.4’中)
文章推荐： How to create a Scrolling Pane for a Chat System?(如何为聊天系统创建滚动窗格？)
文章推荐： ScrollIntoView() Function Not Working In Chrome But Working In Other Browsers(ScrollIntoView()函数在Chrome中不起作用，但在其他浏览器中起作用)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com