From zerobearing2@jmbsoftware.net Mon Dec 1 22:17:52 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 72D8BD1D38B for ; Mon, 1 Dec 2003 22:17:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 02995-01 for ; Mon, 1 Dec 2003 18:17:22 -0400 (AST) Received: from fed1mtao03.cox.net (fed1mtao03.cox.net [68.6.19.242]) by svr1.postgresql.org (Postfix) with ESMTP id 35FF5D1D7E6 for ; Mon, 1 Dec 2003 18:17:20 -0400 (AST) Received: from brook.zerobearing.org ([68.8.137.180]) by fed1mtao03.cox.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20031201221706.MQPG28419.fed1mtao03.cox.net@brook.zerobearing.org> for ; Mon, 1 Dec 2003 17:17:06 -0500 Received: from brook.zerobearing.org (localhost.localdomain [127.0.0.1]) by brook.zerobearing.org (8.12.8/8.12.8) with ESMTP id hB1MHMJo030316 for ; Mon, 1 Dec 2003 14:17:22 -0800 Received: (from apache@localhost) by brook.zerobearing.org (8.12.8/8.12.8/Submit) id hB1MHMBj030314; Mon, 1 Dec 2003 14:17:22 -0800 X-Authentication-Warning: brook.zerobearing.org: apache set sender to zerobearing2@jmbsoftware.net using -f Received: from 206.171.121.15 (SquirrelMail authenticated user david) by webmail.zerobearing.org with HTTP; Mon, 1 Dec 2003 14:17:22 -0800 (PST) Message-ID: <44103.206.171.121.15.1070317042.squirrel@webmail.zerobearing.org> Date: Mon, 1 Dec 2003 14:17:22 -0800 (PST) Subject: XML & Postgres Functions From: "zerobearing2" To: pgsql-sql@postgresql.org Reply-To: zerobearing2@jmbsoftware.net User-Agent: SquirrelMail/1.4.0 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal Message-Id: <20031201221706.MQPG28419.fed1mtao03.cox.net@brook.zerobearing.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/6 X-Sequence-Number: 16172 Hi all- I'm migrating to postgres from the MS SQL Server land, as I can see a great potential with postgres, I was wondering if anyone has experimented or started a project with XML inside user defined functions? I've seen the contrib/xml shipped with the distro, as I see it's usefulness, it's not quite what I had in mind (lacks the ablity to return recordset/table of nodes). Also, the project XpSQL seems interesting, but still not it. I'm looking for a solution similar to the OpenXML that MS implements in their SQL Server. A way to parse XML into a table and/or a temp table for use inside a function. For example, I would like to serialize my objects into XML, send them to a postgres function for saving/updating the relational data inside postgres. I envision something of the following could be useful: XML ------ ...
UDF ------ create function somefunc(xmldata text) begin xmldoc := preparedoc(xmldata); ... update tablename set field1=x.field1, field2=x.field2, from xmltable(xmldoc, '/table/row[@updateflag=1]') as x ... preform removedoc(xmldoc); end; By using XML & XPath, one could be very creative preforming updates to the tables via the xml data passed to the function. By having this functionality, one could have their applications communicate with XML structures instead of database structures and either side could change without drastically effecting the other. Also, by have the application obey XML structures, the database schema is unknown and therefore essentially hidden from the programmers as they don't need to be bothered with how the data is actually stored. While my motivation isn't to hide the database schema from programmers, but to provide simple method for having applications send data to the data tier for storage. It's much easier to define complex object structure in XML and then let the database worry about storing it relationally. I'm not sure if I'm missing the point with postgres, but we've been doing this with MS SQL Server for sometime, while it's not the most effienct method of sending data to the database, I find it most elegant from the application side, simplify code and promotes code reusablity. Has anyone done this before with postgres, if so could you share your experiences? Also, if no one has attempted this, does anyone else share the need for something like this? I'm thinking about starting this project if there is a greater need out there. Best Regards, David zerobearing2@jmbsoftware.net From col@mportal.hu Tue Dec 2 10:28:20 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 63392D1D0AE for ; Tue, 2 Dec 2003 10:28:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89100-07 for ; Tue, 2 Dec 2003 06:27:50 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id B4B3ED1B554 for ; Tue, 2 Dec 2003 06:27:48 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hB2ARlbK066420 for ; Tue, 2 Dec 2003 10:27:48 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hB2AKEQM065587 for pgsql-sql@postgresql.org; Tue, 2 Dec 2003 10:20:14 GMT From: CoL X-Newsgroups: comp.databases.postgresql.sql Subject: Re: XML & Postgres Functions Date: Tue, 02 Dec 2003 11:20:54 +0100 Organization: Hub.Org Networking Services Lines: 53 Message-ID: References: <20031201221706.MQPG28419.fed1mtao03.cox.net@brook.zerobearing.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6a) Gecko/20031029 Thunderbird/0.4a X-Accept-Language: en-us, en In-Reply-To: <20031201221706.MQPG28419.fed1mtao03.cox.net@brook.zerobearing.org> To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/43 X-Sequence-Number: 16209 hi, zerobearing2 wrote: > Hi all- > > I'm migrating to postgres from the MS SQL Server land, as I can see > a great potential with postgres, I was wondering if anyone has > experimented or started a project with XML inside user defined > functions? > > I've seen the contrib/xml shipped with the distro, as I see it's > usefulness, it's not quite what I had in mind (lacks the ablity to > return recordset/table of nodes). Also, the project XpSQL seems > interesting, but still not it. > > I'm looking for a solution similar to the OpenXML that MS implements > in their SQL Server. A way to parse XML into a table and/or a temp > table for use inside a function. For example, I would like to > serialize my objects into XML, send them to a postgres function for > saving/updating the relational data inside postgres. > > I envision something of the following could be useful: > > XML > ------ >
> > ... > >
> > > UDF > ------ > create function somefunc(xmldata text) > begin > xmldoc := preparedoc(xmldata); > .... > update tablename set > field1=x.field1, > field2=x.field2, > from xmltable(xmldoc, '/table/row[@updateflag=1]') as x > .... > preform removedoc(xmldoc); > end; Check the xml dir under contrib. Or/And http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=utf-8&q=xml+in+postgres+site%3Apostgresql.org C.