{"id":99,"date":"2010-01-29T12:12:15","date_gmt":"2010-01-29T18:12:15","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=99"},"modified":"2015-10-28T10:05:47","modified_gmt":"2015-10-28T15:05:47","slug":"mysqls-xml-and-some-xslt","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=99","title":{"rendered":"mysql’s --xml<\/code> and some XSLT (now with JSON!)"},"content":{"rendered":"

Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.<\/p>\n

Both mysqldump and the mysql client have --xml<\/code> options. The output from the cli looks like this:<\/p>\n

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

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

An XSLT processor lets you do something like this:<\/p>\n

mysql --xml -e \"SELECT here\" | xsltproc mysql.xsl -<\/code><\/p>\n

The result will be something like:<\/p>\n

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

If JSON is your thing, you can use xml2json<\/a> to take that output a step farther:<\/p>\n

mysql --xml -e \"SELECT here\" | xsltproc mysql.xsl - | xsltproc xml2json.xsl -<\/code><\/p>\n

Result (whitespace added):<\/p>\n

{\"resultset\" : \r\n  {\r\n    \"statement\" : \"SELECT here\", \r\n    \"row\" : [\r\n      {\"field1\" : \"value a\", \"field2\" : \"value b\"},\r\n      {\"field1\" : \"value c\", \"field2\" : \"value d\"}\r\n    ]\r\n  }\r\n}<\/pre>\n
That will suffice until the mysql client has a –json parameter.<\/p>\n","protected":false},"excerpt":{"rendered":"
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> […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[8],"tags":[],"class_list":["post-99","post","type-post","status-publish","format-standard","hentry","category-mysql-faq"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-1B","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/99","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=99"}],"version-history":[{"count":11,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/99\/revisions"}],"predecessor-version":[{"id":431,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/99\/revisions\/431"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=99"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=99"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=99"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}