Wednesday, June 10, 2009

Advantage PostgreSQL XML power

All of us know the greatness of the opensource database PostgreSQL, which is shortly called as PGSQL. This article outrights the power of XML functions usage available in the postgreSQL database.

Lets take a small sample.

Create a small table

CREATE TABLE "user_add"
id serial NOT NULL,
"name" character(100) NOT NULL,
address character varying(100),
phone character varying (12),
city character varying(20),

now let insert some sample records for our further actions

INSERT INTO "user_add"("name", address, phone,city)
VALUES ( 'John', '10, East st,Chennai', '111-111-111','Texas');

INSERT INTO "user_add"("name", address, phone)
VALUES ( 'Peter', '11, West st,Chennai', '222-222-222','Texas');

INSERT INTO "user_add"("name", address, phone)
VALUES ( 'Mike', '12, North st,Chennai', '333-333-333','Newyork');

now if you do a select of the table as select * from user_add;

1 | John | 10, East st,Chennai | 111-111-111 | Texas
2 | Peter | 11, West st,Chennai | 222-222-222 | Texas
3 | Mike | 12, North st,Chennai | 333-333-333 | Newyork

now I plan to give these data for an web service request or an ajax request for a request of user by city.

Generally we will be getting this data and then will be formulating that as xml format, which generally is a very consuming and exhausting process, PostgreSQL provides a simplistic way to achive this using its power full xml functions

here is a query

SELECT xmlelement (name city,
xmlattributes(city as "city" ),
xmlagg(xmlelement(name user,
xmlattributes(name as name),
(xmlelement(name address,address)),
(xmlelement(name phone,phone))
as xml from user_add where city = 'Texas' group by city;

you should get the xml

So just inaf to be passed on directly. Sounds amazing....

No comments:

Post a Comment