write query results to xml with T-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » write query results to xml with T-sql

write query results to xml with T-sql

Rate Topic

Display Mode

Topic Options

Author

Message

elham_azizi_62

Posted Monday, January 21, 2013 3:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:33 AM
Points: 109, Visits: 390

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

Post #1409463

« Prev Topic | Next Topic »

Permissions