Adding an Element to XML SQL 2008 Query

Question

I am trying to add in a few elements in between the ROOT and PATH in a MSSQL 2008 Query. For Example I generate this something like this:

<Employees>
  <Employee ID="1">
    <LastName>David</LastName>
    <FirstName>Larry</FirstName>
    <Title>Writer</Title>
  </Employee>
  <Employee ID="2">
    <LastName>Colbert</LastName>
    <FirstName>Stephen</FirstName>
    <Title>President of South Carolina</Title>
  </Employee>

With

SELECT 
    [EmployeeID] AS '@ID',
    [LastName], [FirstName],
    [Title]
FROM 
    [dbo].[Employees]
FOR XML PATH('Employee'), ROOT('Employees')

I'd like to add a few elements like this:

<Employees>
<Company>ACME DYNAMITE</Company>
<CreateDate>JAN 01 2013</CreateDate>
  <Employee ID="1">
    <LastName>David</LastName>
    <FirstName>Larry</FirstName>
    <Title>Writer</Title>
  </Employee>
  <Employee ID="2">
    <LastName>Colbert</LastName>
    <FirstName>Stephen</FirstName>
    <Title>President of South Carolina</Title>
 </Employee>

I am using BCP to generate an output file so it would be possible to append a header and footer to the output file. If its possible in the query I'd like to do it that way. I have tried a variety of syntax but just can't seem to get it.

Any help is much appreciated.

Mikael Eriksson · Accepted Answer · 2012-03-08 19:34:46Z

1

SELECT
  'ACME DYNAMITE' as Company,
  'JAN 01 2013' as CreateDate,
  (  
    SELECT
      [EmployeeID] AS '@ID',
      [LastName], 
      [FirstName],
      [Title]
    FROM 
      [dbo].[Employees]
    FOR XML PATH('Employee'), TYPE
  )
FOR XML PATH(''), ROOT('Employees')

answered Mar 8, 2012 at 19:34

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Peddle Over a year ago

Thank you! The empty Path('') was what I wasn't getting.

Collectives™ on Stack Overflow

Adding an Element to XML SQL 2008 Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related