https://www.biostars.org/p/69995/ sra ncbi xslt xsl sql

biostar69995.xsl

<?xml version='1.0'  encoding="ISO-8859-1"?>
<xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0' 
    > 
<xsl:output method="text"/>
<xsl:template match="/">
create table if not exists EXPERIMENT
    (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    alias TEXT,
    center_name TEXT,
    accession TEXT
    );

create table if not exists DESIGN
    (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    experiment_id INTEGER
    );

create table if not exists LIBRARY_DESCRIPTOR
    (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    design_id INTEGER
    );
BEGIN TRANSACTION;
<xsl:apply-templates select="EXPERIMENT_PACKAGE_SET/EXPERIMENT_PACKAGE/EXPERIMENT"/>
COMMIT TRANSACTION;
</xsl:template>

<xsl:template match="EXPERIMENT">
insert into EXPERIMENT(alias,center_name,accession) values
('<xsl:value-of select='@alias'/>','<xsl:value-of select='@center_name'/>','<xsl:value-of select='@accession'/>');
<xsl:apply-templates select="DESIGN"/>
</xsl:template>

<xsl:template match="DESIGN">
insert into DESIGN(experiment_id)  select MAX(id) from EXPERIMENT;
<xsl:apply-templates select="LIBRARY_DESCRIPTOR"/>
</xsl:template>

<xsl:template match="LIBRARY_DESCRIPTOR">
insert into LIBRARY_DESCRIPTOR(name,design_id)  select '<xsl:value-of select='LIBRARY_NAME'/>',MAX(id) from DESIGN;
</xsl:template>


</xsl:stylesheet>