Skip to content

Instantly share code, notes, and snippets.

@shcallaway
Last active May 25, 2021 01:34
Show Gist options
  • Save shcallaway/26a9e66fde42d17c21d9635b0b99c662 to your computer and use it in GitHub Desktop.
Save shcallaway/26a9e66fde42d17c21d9635b0b99c662 to your computer and use it in GitHub Desktop.
How to use XSLT to transform XML files

I had some XML files containing structured data. I wanted to insert this data in to a SQL database. So I needed to figure out how to transform the XML into SQL statements. Turns out, there is something called XLST that can be used to programmatically transform XML files into... well... whatever you want. So here's how I used XSLT to transform XML into SQL statements.

  1. Download Saxon-HE from here: https://saxonica.com/download/java.xml
  2. Create your xml file:
<!-- /Users/sherwood/Code/cdcatalog.xsl -->
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="cdcatalog.xsl"?>
<catalog>
  <cd>
    <title>Empire Burlesque</title>
    <artist>Bob Dylan</artist>
    <country>USA</country>
    <company>Columbia</company>
    <price>10.90</price>
    <year>1985</year>
  </cd>
  <cd>
    <title>Dark Side of the Moon</title>
    <artist>Pink Floyd</artist>
    <country>USA</country>
    <company>Columbia</company>
    <price>10.90</price>
    <year>1973</year>
  </cd>
</catalog>
  1. Create your xsl schema:
<!-- /Users/sherwood/Code/cdcatalog.xsl -->
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:for-each select="catalog/cd">
INSERT INTO cds (title, artist) VALUES (<xsl:value-of select="title"/>, <xsl:value-of select="artist"/>)
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
  1. Run Saxon to transform the xml file:
java -jar /Users/sherwood/Downloads/SaxonHE10-5J/saxon-he-10.5.jar -s:/Users/sherwood/Code/cdcatalog.xml -xsl:/Users/sherwood/Code/cdcatalog.xsl -o:/Users/sherwood/Code/output

You should now have a transformed file!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment