Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-29 : 23:51:21
|
John writes "I am trying to implement a db driven application that uses XML at every interface between tiers. I am relying on SQL Server2K's new "FOR XML EXPLICIT" function to pull XML from the db tier. Some of the queries I need to run are rather expensive, especially since they rely on MSXML. The good thing is that the data returned by the query is rather static and is the perfect candidate to be processed and then cached as XML into a seperate table within SQL Server so that I can just grab from that table instead of rerunning the expensive query.
Caching the data wouldn't be a problem in normal circumstances but I am using the new XML features of the MS server. It appears that I can not insert the result of the FOR XML EXPLICIT query, run from the db tier, back into a different table as text, or any other datatype for that matter. This is a very frustrating limitation.
First off, is it possible to insert the XML as text in the db using the FOR XML EXPLICIT call??? Will MS be implementing this anytime soon and why did they not in the version they released?
If it isn't possible for now, what is the best solution to this problem? I have postulated a couple of options but I am not sure if either hold water. One such solution would be to pass the XML out to the middle-tier and have the application that needs the data also spit the data back up out to the db as text once I convert the stream with an ADO object on that middle tier. I really don't like this option because it forces an extra trip to the db tier. The other option is to write an SP that passes the XML stream out to a dll on the db box which then passes the stream through ADO and finally passes it back to the calling SP as text so that it can be saved into a table.
I look forward to reading your thoughts on this matter. It would be really helpful to get an experts opinion." |
|
|
|
|
|
SQL2K