Title: Generic XML document into a SQL database
Submitter: Astar Seran (other recipes)
Last Updated: 2008/03/09
Version no: 1.0
Category: Miscellaneous

Description:

This XSLT generates SQL queries that saves the given document into a database. Uses host-language calls to perform the query and to obtain generated IDs.

Source: Text Source

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:php="http://php.net/xsl"
  >
  <xsl:output method="html" encoding="windows-1250" indent="no" doctype-system="StrukturaWebu.dtd"/>


  <!-- Vložení dokumentu -->
  <xsl:template match="/">
    <xsl:variable name="sql">
      INSERT INTO documents SET doctype = '<xsl:value-of select="name(./*[1])"/>'
    </xsl:variable>
    <xsl:variable name="doc_id" select="php:function('Insert', $sql )"/>

    <xsl:apply-templates select=" * | @* ">
      <xsl:with-param name="doc_id" select="$doc_id"/>
      <xsl:with-param name="parent_node_id" select="0"/>
    </xsl:apply-templates>
  </xsl:template>


  <!-- Vložení elementu -->
  <xsl:template match="*" name="insert_elem">
    <xsl:param name="doc_id"/>
    <xsl:param name="parent_node_id"/>

    <xsl:variable name="sql">
      INSERT INTO elems SET
      id_parent = <xsl:value-of select="$parent_node_id"/>,
      name = '<xsl:value-of select="name(.)"/>'
    </xsl:variable>
    <xsl:variable name="insert_id" select="php:function('Insert', $sql )"/>

    <xsl:apply-templates select="@* | *">
      <xsl:with-param name="doc_id" select="$doc_id"/>
      <xsl:with-param name="parent_node_id" select="$insert_id"/>
    </xsl:apply-templates>
    
  </xsl:template>

  <!-- Vložení atributu -->
  <xsl:template match="@*" name="insert_attr">
    <xsl:param name="parent_node_id"/>
    <xsl:variable name="sql">
      INSERT INTO attrs SET
      id_elem = <xsl:value-of select="$parent_node_id"/>,
      name = '<xsl:value-of select="name(.)"/>'
    </xsl:variable>
    <xsl:variable name="insert_id" select="php:function('Insert', $sql )"/>
  </xsl:template>

</xsl:stylesheet>

The license for this recipe is available here.

Discussion:

This version works with two tables:

documents - stores references to whole documents.
elems - stores all elements of the XML document.
attributes - stores attributes.

Text nodes are ommited (because I didn't need them), but support for them can be added easily.