5

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.

2 Answers 2

6

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>
1
  • You can also use CASE statements to conditionally add the attributes, e.g. negative numbers in red font
    – Paschover
    Aug 19, 2021 at 13:47
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.