如何在 SQL 中拆分具有相同子子名称的两个 xml 标签 [英] How to break up two xml tags with the same subchild names in SQL

问题描述

我创建了一个脚本，它从 SQL 中的表中获取数据并生成 XML 输出.父、子和子子标签对于 2 个标签都是相同的.SQL 脚本将它们作为一个 XML 值而不是 2 输出.

I have created a script which takes data from a table in SQL and generates an XML output. The parent, child and sub-child tags are all the same which for 2 tags. The SQL script is outputting them as one XML value instead of 2.

 SELECT
 Request.TransactionRef AS [RequestHeader/RequestID],
'Deal.Trial' AS [RequestHeader/Action],
'DoDealValidate' AS [RequestHeader/ActionFlags/Flag],
'DoDealDerive' AS [RequestHeader/ActionFlags/Flag] 

目前的结果是:

<ActionFlags>
<Flag>DoDealValidateDoDealDerive</Flag>
</ActionFlags>




<ActionFlags>
<Flag>DoDealValidate</Flag>
<Flag>DoDealDerive</Flag>
</ActionFlags>

推荐答案

只需在中间放置一些空:

SELECT
 'blah' AS [RequestHeader/RequestID],
'Deal.Trial' AS [RequestHeader/Action],
'DoDealValidate' AS [RequestHeader/ActionFlags/Flag],
NULL AS [RequestHeader/ActionFlags],
'DoDealDerive' AS [RequestHeader/ActionFlags/Flag] 
FOR XML PATH('row');

背景:

引擎通过 SELECT 的列运行并一个接一个地构建它们.

The engine is running through the SELECT's columns and builds them one after the other.

• 好吧，有一个要打开
• 并且有一个要打开
• 再次，还是打开的，没什么
• 下面还有...哦，我们必须关闭并打开一个新的
• 等等...

• Well, there is a <RequestHeader> to open
• and there is a <RequestID> to open
• Again the <RequestHeader>, still open, nothin to to
• and there is <Action> below... Oh, we must close the <RequestID> and open a new <Action>
• and so on...

在您的代码中， 仍然是打开的，因此内容被写入到 open 元素中.

In your code the <Flag> is still open, therefore the content is written into the open element.

我的改变会让引擎思考

• 啊，我们上移一层，所以我们先关闭...哎呀，没什么可写的...
• 现在<Flag>有一些东西，它不再打开了，我们必须重新打开一个(新的)节点
• 等等...

• Ah, we move up one level, so we close the <Flag> first... Oops, there's nothing to write...
• Now there is something for <Flag>, which is not open anymore, we have to re-open a (new) <Flag> node
• and so on...

这篇关于如何在 SQL 中拆分具有相同子子名称的两个 xml 标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！