For XML Path: How to keep Attribute and Value in the same node

Question

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

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

Script 1:

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

Result 1: Only 1 AMOUNT node in root node

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

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;

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

Answer 1

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!

Try it out:

--identical name: You might be surprised

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

--A different name in between

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

--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');

--...and here?

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');