write query results to xml with T-sql

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 21, 2013 at 3:56 am

  #393980

  hello all.

  I need to have one procedure that writes query results to xml.

  please get me t-sql code for this.

  I used this:

  DECLARE @FileName VARCHAR(50)

  DECLARE @SQLCmd VARCHAR(500)

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

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

  SELECT @SQLCmd = 'bcp ' +

  '"SELECT * ' +

  ' FROM [MaliGilan].[dbo].[QMS_QmsBase] ' +

  ' FOR XML PATH(''''), ROOT(''HRQMS_QmsBaseName''), 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

  but get error:Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

  please help me.thanks