Is there a FOR XML variant that can add attributes with arbritrary values?

Question

I'm trying to create a query similar to this

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

To produce something like this-

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>

and it seems like there's no way to create attributes without assigning column values into them.

Answer 1

The query you have

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

concatenates the values to a single Detail element.

<Header>
  <Detail>foobarbaz</Detail>
</Header>

To have multiple Detail elements you need to separate the columns with a null.

SELECT
'foo' AS Detail,
 null,
'bar' AS Detail,
 null,
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail>foo</Detail>
  <Detail>bar</Detail>
  <Detail>baz</Detail>
</Header>

And then you use the @ syntax suggested in the answer by Rob Farley to get the result you are looking for.

SELECT
'A' AS 'Detail/@type', 
'foo' AS Detail,
 null,
'B' AS 'Detail/@type', 
'bar' AS Detail,
 null,
'C' AS 'Detail/@type', 
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>

Answer 2

Try naming your column [@type]

Multiple rows might help too. Something like:

SELECT 'A' as [@type], 'foo'
UNION ALL
SELECT 'B' as [@type], 'bar'
UNION ALL
SELECT 'C' as [@type], 'baz'
FOR XML PATH('Detail'), ROOT('Header');