Sql-server – FOR XML variant that can add attributes with arbritrary values

sql serverxml

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.

Best Answer

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>
Related Question