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
------
<table>
<row field1="Testing" field2="Testing2" updateflag=1/>
...
<row field1="TestingN" field2="TestingN+2" updateflag=1/>
<table>
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
