Store XML RAW result into a temp table / string variable
Hello Friends,
I executed the following command for getting XML result:
SELECT job_id, voyage_id FROM job FOR XML RAW
The result will be in a single column. I need this result to be stored either in a field of temporary table or in a string variable. Is it possible in SQL2000? I came to know it is possible in SQL2005, but giving error in SQL2000.
I executed the following command for getting XML result:
SELECT job_id, voyage_id FROM job FOR XML RAW
The result will be in a single column. I need this result to be stored either in a field of temporary table or in a string variable. Is it possible in SQL2000? I came to know it is possible in SQL2005, but giving error in SQL2000.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
reb73
SQL 2005 supports it due to the availability of the new XML datatype, which is not available in SQL 2000
As reb73 has stated clearly and in answer to your question it cannot be done in SQL Server 2000 without returning the output to the client.
The only reason I did not participate is because you had already been given the answer.
The only reason I did not participate is because you had already been given the answer.
ASKER
Is that a solution or a fact?
It haven't solved my problem. I am not sure about how can I accept this fact as a solution. I need a possible way to make this XML resultset assigned into a string variable without using any external software.
The only possibility which I am having in my mind is save the result set into a text file and read that file using my stored procedure. I think it is possible but will be time consuming operation. What you think?
It haven't solved my problem. I am not sure about how can I accept this fact as a solution. I need a possible way to make this XML resultset assigned into a string variable without using any external software.
The only possibility which I am having in my mind is save the result set into a text file and read that file using my stored procedure. I think it is possible but will be time consuming operation. What you think?
Your question was - "Is it possible in SQL2000?"
I gave you the answer/confirmation quoting references and the reason (XML datatype was introduced only in SQL 2005).
Yes, you could capture the output in a text/xml file using OSQL/ISQL and then read it into a character variable, but there can be other alternatives (a DTS package springs to mind) this can be addressed better if you create a new question provinding clear details of what you seek to accomplish.
I gave you the answer/confirmation quoting references and the reason (XML datatype was introduced only in SQL 2005).
Yes, you could capture the output in a text/xml file using OSQL/ISQL and then read it into a character variable, but there can be other alternatives (a DTS package springs to mind) this can be addressed better if you create a new question provinding clear details of what you seek to accomplish.
ASKER
I am using a stored procedure to read some data from excel sheet and it will process the data accordingly and will result a huge recordset. This need to be inserted into a particular text field of a table which will later used by my application to sent an email for informing the client. Currently this text field is used as body of the email. In my stored procedure what i have to do is insert an html formated content into the text field so that outlook can easily show the content in table format.
>>Is that a solution or a fact?<<
It is a fact.
>>It haven't solved my problem. I am not sure about how can I accept this fact as a solution. <<
To quote from the EE Guidelines:
<quote>
The correct answer to some questions is "You can't do that."
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that answer.
</quote>
It is a fact.
>>It haven't solved my problem. I am not sure about how can I accept this fact as a solution. <<
To quote from the EE Guidelines:
<quote>
The correct answer to some questions is "You can't do that."
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that answer.
</quote>
As reb73 has implied you cannot do that using SQL Server without resorting to :using any external software" or OSQL/ISQL or if you like living dangerously perhaps using the sp_OA* extended Stored Procedures. See this link I posted nearly 5 years ago:
https://www.experts-exchange.com/questions/20911246/Problems-passing-a-long-XML-string-to-OPENXML-using-sp-xml-preparedocument.html
And this one using undocumented functions:
https://www.experts-exchange.com/questions/21086593/Accessing-XML-document-FROM-T-SQL.html
And a great contribution from Hilaire:
https://www.experts-exchange.com/questions/21124599/Saving-XML-output-automatically.html?
This shows some of the pitfalls of the sp_OA functions:
https://www.experts-exchange.com/questions/20946178/sp-OAMethod-fails-when-calling-ADODB-functions-in-runtime-from-a-VB6-DLL.html
Again none of this can be accomplished with T-SQL alone.
https://www.experts-exchange.com/questions/20911246/Problems-passing-a-long-XML-string-to-OPENXML-using-sp-xml-preparedocument.html
And this one using undocumented functions:
https://www.experts-exchange.com/questions/21086593/Accessing-XML-document-FROM-T-SQL.html
And a great contribution from Hilaire:
https://www.experts-exchange.com/questions/21124599/Saving-XML-output-automatically.html?
This shows some of the pitfalls of the sp_OA functions:
https://www.experts-exchange.com/questions/20946178/sp-OAMethod-fails-when-calling-ADODB-functions-in-runtime-from-a-VB6-DLL.html
Again none of this can be accomplished with T-SQL alone.