CODEWITHSUNDEEP
sql-serverxmlbcp
Brian Peddle
Brian Peddle

Reputation: 21

Adding an Element to XML SQL 2008 Query

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.

Upvotes: 2

Views: 1335

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Related Questions