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....
