Want to save xml file passed as input parameter to stored procedure
I have written a stored procedure which takes XML as parameter and I want to store the xml parameter in a file in some folder. Below is what I am doing within the stored procedure: CREATE TABLE ##FooResults (result XML) INSERT INTO ##FooResults(result) values ( @XMLData ) select * from ##FooResults;CREATE TABLE ##FooResults (result XML) INSERT INTO ##FooResults(result) values ( @XMLData ) select * from ##FooResults; it is working until here. then when I start to write in a file as shown below I get the error DECLARE @ExportXmlCommand NVARCHAR(4000) select @ExportXmlCommand= 'bcp.exe master.."select result from ##FooResults" queryout "\\path\foldername\testfile.xml" ' EXEC master..xp_cmdshell @ExportXmlCommand DROP TABLE ##FooResults I have tried putting in the parameters in bcp commandline but still it does not work. Can somebody help me please as I have not used BCP before I am also getting the below error SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'username' please help me!!!!!
Hi there, BCP uses ODBC and as per the error message, I believe the credentials you're using are incorrect or login mode needs change changing to Windows and SQL Server. This article will help: [Microsoft Support] A couple of other things to note. ## means global temporary and will allow access from other queries - do you mean to do that? If you're just accessing this temp table solely in this one query use just one # Did you accidentally paste your query twice or is your code really repeating itself? :
hi thanks for the reply i dont need any temp table .... i used it for testing i need a stored procedure which will accept "@xmldata xml" as input parameter and i want to store this xml string passed as a file in the folder in the stored procedure. i am using sql server management studio 2012 i need something like this: create stored procedure(@xmldata xml) i want to store @xmldata in a file when ever the stored procedure is called. so i can save the file appending datetime to the name of the file and save end please write the code for me as i am new to this... thanks