XSLT and XPath lookup problem

[Patrick Reilly]

I am trying to implement a better method than I already have to document
database schemas with XML, and use XSLT to both generate database DDL
statements (CREATE TABLE, etc) and to transform to HTML for documentation
purposes. In particular I want XSLT to transform to HTML so that my XML
documents can be "live", doing the transform in the browser.

In the new system a very short XML document looks like:

<database>
<table id="PROVIDER">
<field name="KEY" type="decimal" precision="9" scale="0"/>
...
<primary-key id="PKPROVIDER" fields="KEY"/>
</table>

<table id="PERSON">
<field name="PROVIDER"/>
<field name="KEY" type="decimal" precision="9" scale="0"/>
...
<primary-key id="PKPERSON" fields="PROVIDER KEY"/>
<foreign-key id="FKPERSON" fields="PROVIDER" foreign-table="PROVIDER"
on-delete="cascade" on-update="restrict"/>
</table>
</database>

Short description would be:

PROVIDER - a table
PROVIDER.KEY decimal(9,0) (is primary key, so not-null and unique).
Primary key is PROVIDER.KEY

PERSON - a table
PERSON.PROVIDER is foreign key to PROVIDER.KEY, so is same data type.
PERSON.KEY decimal(9,0) is unique number within provider.
Primary key is PERSON.PROVIDER and PERSON.KEY
Foreign key to the PROVIDER table where PERSON.PROVIDER = PROVIDER.KEY

Note that PERSON.PROVIDER doesn't have any type,etc attributes. Since
PERSON.PROVIDER is actually a foreign key to the PROVIDER.KEY field, the
two fields need to have the same data type, and when generating DDL, etc
the data type should be looked up from the field that this foreign field
references. Therefore PERSON.PROVIDER's data type should be listed as the
same as PROVIDER.KEY's type,precision,etc. A "gotcha" is that
PERSON.PROVIDER could possibly be listed in more than one foreign-key
element, so I'd have to only follow the first one found.

I'm trying to write a template (mode="datatype") for a field node which
outputs the actual type,precision,length,etc values for a field. In the
case of PROVIDER.KEY that's easy, because it is explicitly defined by the
attributes for the node. But for PERSON.PROVIDER I can't figure it out. So
far my template looks like:

<xsl:template match="field" mode="datatype">
<xsl:choose>
<!-- If type is present, go ahead with the data type -->
<xsl:when test="@type">
<xsl:choose>
<!-- If length present, write out type(length) -->
<xsl:when test="@length"><xsl:value-of
select="@type"/>(<xsl:value-of select="@length"/>)</xsl:when>
<xsl:when test="@precision">
<!-- If precision is present... -->
<xsl:choose>
<!-- If scale is also present, write out type(precision,scale)
-->
<xsl:when test="@scale"><xsl:value-of
select="@type"/>(<xsl:value-of select="@precision"/>,<xsl:value-of
select="@scale"/>)</xsl:when>
<!-- Otherwise write out type(precision) -->
<xsltherwise><xsl:value-of select="@type"/>(<xsl:value-of
select="@precision"/>)</xsltherwise>
</xsl:choose>
</xsl:when>
<!-- No length,precision,scale - write out type -->
<xsltherwise><xsl:value-of select="@type"/></xsltherwise>
</xsl:choose>
</xsl:when>
<!-- Otherwise, assume it is a foreign key and use the foreign field
instead -->
<xsltherwise>
????????????
</xsltherwise>
</xsl:choose>
</xsl:template>

Any help appreciated.

 

[Dimitre Novatchev]

Use:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="text"/>

<xsl:template match="table">
<xsl:value-of select="concat('
', @id, ' - a table')"/>
<xsl:apply-templates/>
</xsl:template>

<xsl:template match="field" >

<xsl:variable name="vRefTable"
select="/*/table[@id
= current()/../foreign-key
[@fields=current()/@name]
/@foreign-table
]"/>

<xsl:variable name="vRef"
select="self::*[@type]
|
$vRefTable/field
[@name
=
$vRefTable/primary-key/@fields
]"/>

<xsl:value-of select="concat(@name, ' ')"/>
<xsl:choose>
<!-- If length present, write out type(length) -->
<xsl:when test="$vRef/@length">
<xsl:value-of select="$vRef/@type"/>
<xsl:value-of select="concat('(',$vRef/@length, ')')"/>
</xsl:when>
<xsl:when test="$vRef/@precision">
<!-- If precision is present... -->
<xsl:choose>
<!-- If scale is also present, write out type(precision,scale)
-->
<xsl:when test="$vRef/@scale">
<xsl:value-of select="$vRef/@type"/>
<xsl:value-of select="concat('(',$vRef/@precision, ',',
$vRef/@scale,')')"/>
</xsl:when>
<!-- Otherwise write out type(precision) -->
<xsltherwise>
<xsl:value-of select="$vRef/@type"/>
<xsl:value-of select="concat('(', $vRef/@precision, ')')"/>
</xsltherwise>
</xsl:choose>
</xsl:when>
<!-- No length,precision,scale - write out type -->
<xsltherwise>
<xsl:value-of select="$vRef/@type"/>
</xsltherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>


When this transformation is applied on your source.xml:

<database>
<table id="PROVIDER">
<field name="KEY" type="decimal" precision="9" scale="0"/>
<primary-key id="PKPROVIDER" fields="KEY"/>
</table>
<table id="PERSON">
<field name="PROVIDER"/>
<field name="KEY" type="decimal" precision="9" scale="0"/>
<primary-key id="PKPERSON" fields="PROVIDER KEY"/>
<foreign-key id="FKPERSON" fields="PROVIDER" foreign-table="PROVIDER"
on-delete="cascade" on-update="restrict"/>
</table>
</database>

the wanted result is produced:

PROVIDER - a table
KEY decimal(9,0)



PERSON - a table
PROVIDER decimal(9,0)
KEY decimal(9,0)



=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL