How to add extra atrributes in a FOR XML PATH statement

Question

Is there a way to add an additional attribute as below?

Before...

  <Event id="CE1127552523644210147">
    <Title>General Surgery Orange Rotation </Title>
    <Duration>671</Duration>
    <InstructionalMethod>Clinical Rotation</InstructionMethod>
  </Event>

After:

  <Event id="CE1127552523644210147">
    <Title>General Surgery Orange Rotation </Title>
    <Duration>671</Duration>
    <InstructionalMethod Primary='True'>Clinical Rotation</InstructionMethod>
  </Event>

Original Query:

select 
    id as '@id',
    Title,
    Duration,
    InstructionalMethod
from MyTable
for XML PATH ('Event'), ROOT('Events')

Based on search on Stack I did try this but no data returned for the element.

select 
    id as '@id',
    Title,
    Duration,
    'True' AS 'InstructionalMethod/@Primary'
from mytable
for XML PATH ('Event'), ROOT('Events'), TYPE

Result:

  <Event id="CE1127552523644210147">
    <Title>General Surgery Orange Rotation </Title>
    <Duration>671</Duration>
    <InstructionalMethod Primary="True" />
  </Event>

Thanks for your help.

Brian

Answer 1

You're close - but if you want the element, you have to have that line in there, too!

Try this:

SELECT
    id as '@id',
    Title,
    Duration,
    'True' AS 'InstructionalMethod/@Primary',
    InstructionalMethod   -- add this line to get the actual value as element
FROM 
    dbo.mytable
FOR XML PATH ('Event'), ROOT('Events'), TYPE