Using xsl to import a phpmyadmin xml file

Yep, following the last post I have to explain what I just did for the LazyWeb1 . This is going to be boring and technical, folks.

If you’re like me, you’ve exported a MySQL database as an xml file through PhpMyAdmin. In general, you shouldn’t do this. You want the SQL file. I don’t entirely understand it, but phpmyadmin does not import the format that it exports. If you throw away the database, you’ll be stuck with an xml file that can’t be easily imported. Okay, and if you already threw away the database? In my case, it’s an old Texpattern db that I want to get into a WordPress db, so if I can just get it into RSS form I’m home free. Here’s what you do:

  • Take a look at this explanation of XSL (XML Stylesheets).
  • Create an XSL stylesheet like this (name it stylesheet.xsl for this example):
    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
    <rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/">
    <channel>
    <title>Stupididea</title>
    <link>http://www.stupididea.com/</link>
    <description></description>
    <language>en-us</language>
    <xsl:for-each select="stupidid_text/textpattern">
    <item>
    <title><xsl:value-of select="Title"/></title>
    <link></link>
    <description><xsl:value-of select="Body_html"/></description>
    <dc:creator><xsl:value-of select="AuthorID"/></dc:creator>
    <dc:date><xsl:value-of select="Posted"/></dc:date>
    </item>
    </xsl:for-each>
    </channel>
    </rss>
    </xsl:template>
    </xsl:stylesheet>
  • Include a line like this at the top of the xml file you exported from phpmyadmin:
    <?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>
  • Open an xsl-aware browser (most of them) and try to save the page as an rss file. Note: this was quirky for me. Sometimes the browser would just save the original file, sometimes it would save the stylesheeted output. Try with different browsers.
  • (WordPress) Import the RSS file after checking that it is valid.

This is the general solution for any case where you can recover your database by transforming the phpmyadmin xml into an importable format. You’ll have to play around with the options based on your use-case.

---

  1. Ironically, there doesn’t appear to be an entry on Wikipedia for Lazyweb. I understand it as asking on your blog for help with answering a question (“Dear Lazyweb, …”), i.e., you’re too lazy to research the topic yourself. I’m extending the concept of LazyWeb here to include the posts and forum threads that help you when you’re out googling for a solution to a problem. [back]