How to transform results of a select statement formatted as XML?

Question

When I run the command

mysql EvePlanataryExpenses --xml -uuser -p -e "SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth;" |sed '/^\s*$/d'

I get the following output

<?xml version="1.0"?>
<resultset statement="SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="sTransactionDateTime">2016-10-13</field>
    <field name="profit">1177089967.25</field>
  </row>
</resultset>

Is it possible to remove the statement="text" from the root element, and set the field name to something else than <field name=""></field> from the SQL query itself?

I saw something here that I thought I could use, but can't make it work.

Or would I need to run the output through a parser (Java, PHP, something else) and get that to remove the statement from the root element and rename the field elements?

Answer 1

The query doesn't know (or care) how its results might be formatted by the client (the mysql program in this case), so no, you cannot influence the XML in the query itself. What you're looking for is called XSL transformation, for which there are command line tools (e.g. xmlstarlet) through which you can pipe your output.