appending XML encoding to query output

• jjonsenj

  Grasshopper

  Points: 11

  More actions

  May 26, 2016 at 6:06 am

  #104355

  Hi People

  So i'm having a bit of trouble to figure out how i could make this work so that i can append the xml encoding to my query.

  this is what i hacked together so far:

  DECLARE @FileName VARCHAR(50)

  DECLARE @SQLCmd VARCHAR(8000)

  SELECT @FileName = 'C:\SampleXMLOutput.xml'

  -- in this command, we are making sure there is only one ROOT node

  SELECT @SQLCmd = + 'bcp ' +

  '"SELECT Id, Initials, firstname, lastname, email ' +

  ' FROM Employees.dbo.IDCards ' +

  ' FOR XML PATH(''Employee''), ELEMENTS, ROOT(''Employees''), TYPE "' +

  ' queryout ' +

  @FileName +

  ' -w -T -S' + @@SERVERNAME

  -- display command, for visual check

  SELECT @SQLCmd AS 'Command to execute'

  -- create the XML file

  EXECUTE master..xp_cmdshell @SQLCmd

  I've gotten this as an example, but can't make it work with the code above..

  SELECT CONVERT(XML, '<?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>'),

  (

  SELECT

  'Test' AS Test,

  'SomeMore' AS SomeMore

  FOR XML PATH('TestPath')

  )

  FOR XML PATH('')

  Hope one of you Gurus has an idea how to achieve this?

  JJ