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),
CONSTRAINT id PRIMARY KEY (id)
)
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....
