对于XML路径:如何将属性和值保留在同一节点中 [英] For XML Path: How to keep Attribute and Value in the same node

问题描述

使用FOR XML PATH时出现一些问题.我的情况是:

I have some problem when using FOR XML PATH. My situation is:

我已如下运行脚本，属性CCY和AMOUNT的值合并到同一节点:

I have run the script as below, the attribute CCY and value of AMOUNT is merged to the same node:

脚本1:

SELECT 'USD' AS 'Amount/@Ccy',
       123000 AS Amount,
       'Foo' AS Foo
FOR XML PATH('root'), TYPE;

结果1:root节点中只有1个AMOUNT节点

Result 1: Only 1 AMOUNT node in root node

<root>
  <Amount Ccy="USD">123000</Amount>
  <Foo>Foo</Foo>
</root>

脚本2:我将Foo的顺序更改为中间，结果是 错误

Script 2: I change the order of Foo to the middle, and the result is wrong

SELECT 'USD' AS 'Amount/@Ccy',
       'Foo' AS Foo,
       123000 AS Amount
FOR XML PATH('root'), TYPE;

结果2:根节点中有2个节点AMOUNT

Result 2: There are 2 nodes AMOUNT in the root node

<root>
  <Amount Ccy="USD" />
  <Foo>Foo</Foo>
  <Amount>123000</Amount>
</root>

所以我的问题是:如何将它们放置在同一节点中而不将它们放在一起.谢谢

So my question is: How to keep them in the same node without putting them together. Thanks

推荐答案

这意味着它可以工作...

This is at it is meant to work...

引擎在列列表中向下移动，打开一个元素，填充嵌套的元素，找到一个新元素(哦！我必须关闭最后一个元素！)...等等.

The engine is travelling down the column list, opens an element, fills in nested elements, finds a new element (Oh! I have to close the last one!)... and so on.

必须在包含元素之前声明属性.顺序很重要！

An attribute must be stated before the containing element. The order matters!

尝试一下:

-相同的名称:您可能会感到惊讶

--identical name: You might be surprised

SELECT 1 AS [SomeElement]
      ,2 AS [SomeElement]
FOR XML PATH('SomeTag'),ROOT('root');

-两者之间的不同名称

SELECT 1 AS [SomeElement]
      ,'in between' AS [SomeOther]
      ,2 AS [SomeElement]
FOR XML PATH('SomeTag'),ROOT('root');

-不同的元素是-很好-不存在(您也可以使用NULL)

--The different element is - well - not there (you can use NULL as well)

SELECT 1 AS [SomeElement]
      ,''
      ,2 AS [SomeElement]
FOR XML PATH('SomeTag'),ROOT('root');

-您认为这里会发生什么?

--What do you think what will come out here?

SELECT 'blah' AS [SomeElement/@TheAttribute]
      ,1 AS [SomeElement]
      ,2 AS [SomeElement]
FOR XML PATH('SomeTag'),ROOT('root');

--...在这里?

SELECT 'blah' AS [SomeElement/@TheAttribute]
      ,1 AS [SomeElement]
      ,''
      ,'blub' AS [SomeElement/@TheAttribute]
      ,2 AS [SomeElement]
FOR XML PATH('SomeTag'),ROOT('root');

-终于-这是您的示例(原则上)

--This is - finally - your example (in principles)

SELECT 1 AS [SomeElement]
      ,'in between' AS [SomeOther]
      ,'blub' AS [SomeElement/@TheAttribute]
FOR XML PATH('SomeTag'),ROOT('root');

-而且-只是为了好玩！ -也可以尝试

--And - just for fun! - try this too

SELECT 1 AS [SomeElement]
      ,NULL
      ,'blub' AS [SomeElement/@TheAttribute]
FOR XML PATH('SomeTag'),ROOT('root');

这篇关于对于XML路径:如何将属性和值保留在同一节点中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！