| Author |
Topic |
|
whiteshadow
Starting Member
15 Posts |
 Posted - 2005-04-15 : 04:46:58
|
| Hi, Can someone help me on my problem. How could I accomplish saving the result of the query of my temporary table into a XML file.use pubsdeclare @path char(100), @query char(200)CREATE TABLE #tablename(FIELD1 CHAR(20))INSERT INTO select #tablename VALUES('test')SELECT * FROM #tablename >>>>> How could I save this to an XML file? Thanks a lot of your time and effort. |
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-15 : 21:09:40
|
| Why would you want to store database info in an xml file when it is available from the database to begin with?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
 |
|
|
whiteshadow
Starting Member
15 Posts |
Posted - 2005-04-15 : 21:15:07
|
| Hi, Thanks for your reply. I need an XML file from my query, it is part of our client's request. I could not change it for this is what they want. They have an old application that would use these XML files. Is this possible? And how to this? Thanks in advance for your time and effort. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-15 : 21:28:37
|
| The basic premise behind this is flawed; once you save the data to an xml file, that data is outdated. Any change to the database will not be reflected in the saved file. You say this is an old application, but it uses XML files? How did the app access data in the first place?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
whiteshadow
Starting Member
15 Posts |
Posted - 2005-04-15 : 21:36:25
|
| Hi, Thanks for for your reply. You may think that I'm doing all wrong in my application. I'm sorry if I have misleaded you by saying an old application, I mean they have an existing application. I can't argue with what they want and why they want it that way. Just right now I'm faced with this problem and I need a solution for this problem. Is this possible or not? Thanks in advance for your time and effort. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-15 : 21:42:31
|
| Ok,can the app access the database now?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
whiteshadow
Starting Member
15 Posts |
Posted - 2005-04-15 : 21:53:35
|
| Hi, I don't understand your question. I should create a stored procedure for this one. And the stored procedure would create the XML file. Anyway I got a solution for my problem by doing this:declare @path char(100), @query char(200)CREATE TABLE ##tablename(FIELD1 varCHAR(20))INSERT INTO ##tablename values ('dax')set @path = 'c:\dax.xml'set @query = 'bcp "select * from ##tablename FOR XML AUTO" queryout ' + @path + ' -T -c -r -t'execute master..xp_cmdshell @query drop table ##tablename I have some uncertainties again because this uses a Global Temporary Table, can I lock my Global Temporary Table so that in case two users would access the stored procedure, they would not override the other global temporary table? Is there any locking to be used here? Thanks in advance for your time and effort. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-15 : 22:19:31
|
Let me rephrase this:Can the app execute the stored procedure you are writing? Most XML based applications can access SQL data directly, and therefore you would not need to save to a file. I assume that the app cannot do this (or you wouldn't be looking to do this), but if that is the case, how do you intend to execute the procedure? Your code is ok, but the concern you have with multiple users accessing the data at the same time is the same as my original post: quote:
The basic premise behind this is flawed; once you save the data to an xml file, that data is outdated.
This is not the best way to go about this. For a one-time function, yeah, but you would be better off getting the app to access the DB directly. If it is XML, then this should be (or probably already is) possible. As for locking a table, search BOL using "locking, table-level"AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
whiteshadow
Starting Member
15 Posts |
Posted - 2005-04-15 : 22:33:52
|
| Hi, Thanks again for your reply. My stored procedure would be called by another stored procedure from a different database (Linked Server) in which their stored procedure was called by their existing application. Thank you Andy for enlightening me, you’ve been such a great of help. More power to you. :) |
|
|
|
|
Saving to data into XML file