asked
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][1] 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? [1]:
https://support.microsoft.com/en-gb/help/2121736/error-message-when-you-try-to-authenticate-an-odbc-connection-to-your
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
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
Hi Radhika, I currently don't have access to SSMS to help you out in more detail, however, if you're new to this then your best way to look at the BCP Utility on Microsoft:
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017 From this you'll work out that you need -x as switch for xml and you'll also need to specify out or out_file. However, I feel that you also have an issue with ODBC based on the above error, so you might need to add switches for mixed mode, server instance etc. Regards, Dave.
I want to pass xml string as parameter to a web service method in visual studio using c# and then store the xml string in a file (txt or xml or any extension) for example when I run the below code it works as I have my xml string in the code, [WebMethod] public string savexml( ) strConnectionString = new SqlConnection(constring); try { strConnectionString.Open(); XmlDocument doc = new XmlDocument(); doc.LoadXml(" asdadsa "); using (XmlTextWriter writer = new XmlTextWriter(@"Q:\data1.xml", null)) { writer.Formatting = Formatting.Indented; doc.PreserveWhitespace = true; doc.Save(@"Q:\data1.xml"); } strConnectionString.Close(); return "file created"; } but when I pass the xml string as " asdadsa " to a web method like shown below public string savexml(string xmlstr ) and change the line doc.LoadXml(" asdadsa "); to doc.LoadXml(xmlstr); it does not work ...I get the error please help
18 People are following this question.
