A Little Noise

Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.

Both mysqldump and the mysql client have --xml options. The output from the cli looks like this:

<resultset statement="SELECT here">
  <row>
    <field name="field1">value a</field>
    <field name="field2">value b</field>
  </row>
  <row>
    <field name="field1">value c</field>
    <field name="field2">value d</field>
  </row>
</resultset>

I wanted to change those <field name=”fieldName”> into <fieldName> tags, and all it takes is a little XSLT, which I’ve uploaded here.

An XSLT processor lets you do something like this:

mysql --xml -e "SELECT here" | xsltproc mysql.xsl -

The result will be something like:

<resultset statement="SELECT here">
  <row>
    <field1>value a</field1>
    <field2>value b</field2>
  </row>
  <row>
    <field1>value c</field1>
    <field2>value d</field2>
  </row>
</resultset>

If JSON is your thing, you can use xml2json to take that output a step farther:

mysql --xml -e "SELECT here" | xsltproc mysql.xsl - | xsltproc xml2json.xsl -

Result (whitespace added):

{"resultset" : 
  {
    "statement" : "SELECT here", 
    "row" : [
      {"field1" : "value a", "field2" : "value b"},
      {"field1" : "value c", "field2" : "value d"}
    ]
  }
}

That will suffice until the mysql client has a –json parameter.