| Author |
Topic |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2006-02-01 : 22:13:19
|
| Hi,Total newbie to SQL server, asp, you name it, so please pardon the many stupid questions and, what I'm sure will be, a poor explanation of what I've been assigned to do. We have a web application that enables users to query, via an .asp program, an XML repository file (it's created on the weekend via a process that extracts data from various tables, converts the data into XML, and writes the XML to the repository file). I've been asked to develop a process to eliminate the XML repository file (taking up too much disk space) by having the .asp program somehow create it on the fly, and return it to the user screen. My first attempt at doing this hit a wall. Any suggestions on how I might do this ?Thanks,Jeff |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-02-01 : 22:40:26
|
| Any chance you can eliminate the whole "file repository" idea and just go direct to the database?Nathan Skerl |
 |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-01 : 22:49:06
|
| Actually, if I'm understanding your question, that is what I'm hoping to do is eliminate the XML repository file. I'd like to access database via the .asp program, convert the data to XML on the fly and then return the xml data to the user screen.Thanks,Jeff |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-02-02 : 00:08:56
|
| I guess Im asking why the XML middle man? Users can query the database directly from asp through stored procedures. Are you trying to return XML to the user? Or are you simply trying to return a dataset? Can you give us detailed example of a typical scenario? What kind of data is this? How will users query the tables? Etc...Thanks!Nathan Skerl |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-02 : 07:57:49
|
| Sorry, yes the request is to return XML to the user screen on the fly as opposed to how it is returned now, from the XML repository file. Here is part of the .asp program currently in production. We connect to the Sybase database and then...sql = "SELECT XMLDoc, isVisible FROM XMLRepository WITH (NOLOCK) WHERE hdr_ctl_nbr = '" & sID & "'" & _ " AND transtype = '837" & sCtype & "'" set rsXML = server.CreateObject("adodb.recordset")rsXML.Open sql, sConn, adOpenForwardOnly, adLockReadOnly...... styleFile = Server.MapPath("xsl/xrfvstyle.xsl") set source = Server.CreateObject("Microsoft.XMLDOM") source.async = false source.loadxml(rsXML("XMLDoc")) set style = Server.CreateObject("Microsoft.XMLDOM") style.async = false style.load(styleFile)' response.write (rsXML("XMLDoc")) Response.Write REPLACE(source.transformNode(style),"|","<BR>")[/b]Hope this answers your questions. The xml repository file is created on the weekends via stored procedures and I had hoped to clone that process and return the XML data to the user screen...Thanks,Jeff |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-02-02 : 12:16:48
|
| Jeff,For Sybase questions you might find your answer faster by posting this question at http://www.dbforums.com/ in the Sybase forum as SQLTeam is a MS SQL Server focused forum.Sorry I couldnt be more help :)Nathan Skerl |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-03 : 07:42:08
|
| No problem, thanks very much Nathan ! I will check out the forum you referred me to.Jeff |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-02-03 : 13:50:49
|
| just for posterity or those that end up reading this besides Jeff, who are using SQL Server ... SQL Server can return XML directly by using the SELECT ... FOR XML options. There are several options that can be utilized depending on the end needs. SQL 2005 offers even more options for dealing with (reading and writing) XML. |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-03 : 14:28:38
|
| Thanks very much druer !Jeff |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-02-03 : 14:58:27
|
Of course, I didnt mean to imply that SQL Server could not push the XML :)Also, looking at your asp code, I would highly reccommend you packaging that query as a stored procedure and use parameters for sID / sCtype variables. Aside from the performance benefits of using stored procs (Sybase has these, correct?), ad hoc sql used like that in the asp is asking for SQL injection attacks.And it appears from this code:SELECT XMLDoc, isVisible FROM XMLRepository WITH (NOLOCK)... That you are already storing formatted xml in your database. I guess something else is constructing the xml and persisting it in the Sybase db?Nathan Skerl |
|
|
|
|
XML on the Fly