如何在 SQL 中拆分具有相同子子名称的两个 xml 标签

2021-10-01 00:00:00 xml xpath tsql sql-server for-xml

我创建了一个脚本，它从 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...

相关文章