Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. This util has been very helpful to me in developing a document mangement application. Rather than writing insert/update logic every time the db or xml schema changes, I just tweak the config file and it will generated the inserts, updates, and deletes by comparing the XML document with the tables to which the XML elements are mapped. I've been able to handle tree structures tolerably well. I am currently rewriting the code in C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:02]: Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. XSLT could be used to convert virtually any xml table format directly into an insert statement. For me, this is better than using a programming language plus a parser. XSLT is quite powerful and fast and is build on top of xpath, and is a closer fit to the declarative programming model of sql. Validation could be done at the xslt stage or with schemas, which I prefer. XSLT, or Perl, or anything. That's not a problem. It becomes a problem when I have to hand write insert/update statements for every type of element in an XML document. person first-nameAlan/first-name last-nameGutierrez/last-name ssn1234565789/ssn /person If I feed this document to a database I want it to absorb the document, inserting if doesn't already exists, updating it if it does. There is no way to test for the existstence of a record in a person table during an XSLT transformation. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:03]: This is just about a total conversion of the backend to an xml document server. The marriage of xml and sql is awkward and not easily retrofitted to existing databases. Its pretty much proven that hierarchal storage techniques (xml included) are more difficult to manage and use than traditional sql databases. However, xml does have some very powerful supplemental technologies for document generation on the client end, especially xslt. Unless there is a compelling reason to use those tools, you are 99% likely better off not using xml at all. XML has also found a niche in the edi world, but in this case you can get away with using the blob technique below. I suppose this was in response to my comments, hard to tell with all the top posting... I am not advocating adding XML storage to the PostgreSQL backend. XML is just a stupid little tag language. PostgreSQL is so much more. I adopted XML in my application to generated HTML via XSLT in Internet Explorer. XSLT is very useful when the interface is expressed in HTML. It has been as good a format as any for transmitting documents and storing them on file system. Yes, mapping XML to SQL requires developer intervention. The little program I wrote makes it very simple to express the mapping from and XML element to db table. It seems like it might be a useful add on. Maybe it could be kept with the back end, and written in C. Getting the information out PostgreSQL has been simple, I've used Perl. Getting information back in has been simple with my utility which will check if the element is already stored as a row, and update, otherwise it will insert. It can also delete rows based on the document and keep track of placeholder values used by the client program to represent SERIAL values in database. What I've got is a system where I post an XML document to a URL and it gets mapped out to my normalized PostgreSQL schema. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. It has some constraints, but it is a very useful utility. I've been able to abosorb XML documents into my PostgreSQL db just by tweeking the configuration file. Currently, I am porting it to C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
