creating XML file from T-SQL query

• jeff.farr 88997

  SSC Enthusiast

  Points: 160

  More actions

  April 15, 2015 at 7:38 am

  #306975

  Hi All,

  I'm missing something here.

  What I am trying to recreate is:

  <value version="5" type="database">

  <name>master</name>

  <server>servername</server>

  <integratedSecurity>True</integratedSecurity>

  <connectionTimeout>15</connectionTimeout>

  <protocol>-1</protocol>

  <packetSize>4096</packetSize>

  <encrypted>False</encrypted>

  <selected>True</selected>

  <cserver>ServerAlias</cserver>

  </value>

  with this query:

  SELECT

  'version="5" type="database"' AS 'value',

  'master' AS 'name',

  LTRIM(RTRIM(([Server Name]))) AS 'server',

  'True' AS 'integratedSecurity',

  15 AS 'connectionTimeout',

  4096 AS 'packetSize',

  'False' AS 'encrypted',

  'True' AS 'selected',

  LTRIM(RTRIM(([Server Name]))) AS 'cserver'

  FROM dbo.RedGateServerList

  FOR XML PATH('value'), ELEMENTS

  BUt my output is not correct, it is creating this:

  <value>

  <value>version="5" type="database"</value>

  <name>master</name>

  <server>ServerName</server>

  <integratedSecurity>True</integratedSecurity>

  <connectionTimeout>15</connectionTimeout>

  <packetSize>4096</packetSize>

  <encrypted>False</encrypted>

  <selected>True</selected>

  <cserver>ServerAlias</cserver>

  </value>

  So my question is how to I get <value>version="5" type="database"</value>

  as the first 'value' node?

  I've tried multiple ways, but no success.

  Thanks in advance

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 15, 2015 at 7:42 am

  #1791102

  SELECT '5' AS 'value/@version',

  'database' AS 'value/@type',

  'master' AS 'value/name',

  LTRIM(RTRIM(( [Server Name] ))) AS 'value/server',

  'True' AS 'value/integratedSecurity',

  15 AS 'value/connectionTimeout',

  4096 AS 'value/packetSize',

  'False' AS 'value/encrypted',

  'True' AS 'value/selected',

  LTRIM(RTRIM(( [Server Name] ))) AS 'value/cserver'

  FROM dbo.RedGateServerList

  FOR XML PATH(''), ELEMENTS;

  Produces: -

  <value version="5" type="database">

  <name>master</name>

  <server>RAWR</server>

  <integratedSecurity>True</integratedSecurity>

  <connectionTimeout>15</connectionTimeout>

  <packetSize>4096</packetSize>

  <encrypted>False</encrypted>

  <selected>True</selected>

  <cserver>RAWR</cserver>

  </value>

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• jeff.farr 88997

  SSC Enthusiast

  Points: 160

  More actions

  April 15, 2015 at 7:46 am

  #1791103

  Thanks for the response.