We are trying to get all the data from MySQL table as an XML (Or) Consider we have a table says employees which have an approx of 2000 employee details in it and you are developing a mobile application to show the list in that which would use XML parsing to get data from the server.
So, what a server-side developer would do to get all of them as an XML?
Conventionally any programmer will use a for or while loop to get this done and renders the XML data layout the response buffer. i.e., as in the following steps
- select * from employees is executed to get all data.
- start the XML header
- A loop is executed all over 2000 records and render each record as an xml node.
- end the XML
This becomes a big burden for the server. Also can cause overhit on the server causing a delay in responding to the other users. To balance the server load we can delegate this work to DB Server, If you happen to use MySQL as DB Server, it provides a fine method set concat and group_concat
SET @xml:=''; SELECT @xml:=concat(@builtxml,"",emp.number,"",emp.name,"") "XML" FROM employees emp,(SELECT @builtxml:='') a ORDER BY @builtxml DESC LIMIT 0,1