Subject: Re: [xsl] I need tools XML/XSL/XSD for generation os SQL JOIN...Again: From: Jeni Tennison <jeni@xxxxxxxxxxxxxxxx> Date: Thu, 1 Nov 2001 10:34:16 +0000 |
Hi Cristobal, > I need urgently tools ( XML/XSL/XSD) for generate automaticaly JOINs > of varias table schema BD.. It looks as though you have the XML. An XML Schema file probably won't help you here. But you could use XSLT to create the XML that you want as the output. > 1) A file XML (XSD preference) must exists for definitions of tables and > relations ship's (PKs,FKs...) You can get hold of TablasRelaciones.XML from within the XSLT using the document() function: document('TablasRelaciones.XML') The main kind of access that you require from this file is to retrieve the Tabla elements according to their Id attribute. To make this easier, you should create a key that indexes the Tabla elements as follows: <xsl:key name="Tablas" match="Tabla" use="@Id" /> If you do that then you can use the key() function to retrieve Tabla elements by Id. For example, to get the Tabla element for 'LOOKUP_ALUMNOS_SEL' you would do: key('Tablas', 'LOOKUP_ALUMNOS_SEL') The only thing that you have to be a bit careful about is that the context node when you use the key() function has to be in the TablasRelaciones.XML document, so depending on the situation you might have to use an xsl:for-each to change the current node: <xsl:variable name="Id" select="..." /> <xsl:for-each select="document('TablasRelaciones.XML')"> ... key('Tablas', $Id) ... </xsl:for-each> > 2) My input is an XML file with the tables target The document element of your source XML file is an Argums element, so I'd create a template matching that element: <xsl:template match="Argums"> ... </xsl:template> The File element child of the Argums element gives you the name of the file containing the relationships between the tables. That's the one that you want to retrieve with the document() function. I'd set up a variable to hold its root node, as follows (the second argument to the document() function ensures the file reference is resolved relative to the source document rather than the stylesheet): <xsl:template match="Argums"> <xsl:variable name="TablaDefs" select="document(File, .)" /> ... </xsl:template> >From this file, you want to retrieve the Tabla elements whose Ids are those listed in the child Tabla elements of the Argums element. First make a variable to hold the Tabla Ids: <xsl:template match="Argums"> <xsl:variable name="TablaDefs" select="document(File, .)" /> <xsl:variable name="TablaIds" select="Tabla" /> ... </xsl:template> Then change the context to the $Tablas document and retrieve all the relevant Tabla element using the key() function, putting them into a $Tablas variable: <xsl:template match="Argums"> <xsl:variable name="TablaDefs" select="document(File, .)" /> <xsl:variable name="TablaIds" select="Tabla" /> <xsl:for-each select="TablaDefs"> <xsl:variable name="Tablas" select="key('Tablas', $TablaIds)" /> ... </xsl:for-each> </xsl:template> Then you want to create various static output, which you can do directly with literal result elements: <xsl:template match="Argums"> <xsl:variable name="Tablas" select="document(File, .)" /> <xsl:variable name="TablaIds" select="Tabla" /> <xsl:for-each select="TablaDefs"> <xsl:variable name="Tablas" select="key('Tablas', $TablaIds)" /> <SQL Tipo="JOIN"> <SELECT /> <FROM> ... </FROM> <WHERE> ... </WHERE> </SQL> </xsl:for-each> </xsl:template> The content of the FROM element is a list of the Tabla Ids and IdNums. You can create this by iterating over the content of the $Tablas variable and accessing their Id and IdNums attributes, as follows: <xsl:for-each select="$Tablas"> <Tabla> <xsl:value-of select="@Id" /> <xsl:text> </xsl:text> <xsl:value-of select="concat('T', @IdNum)" /> </Tabla> </xsl:for-each> The content of the WHERE element is a bit more complex. For each Tabla in $Tablas, you need to look at its child FK elements and see whether the Id of the referenced Tabla is the same as the Id of one of the Tablas in $Tablas. If so, then you need to generate the line based on the Col element. You can do this with the following: <xsl:for-each select="$Tablas/FK"> <xsl:variable name="Id" select="concat('T', ../@IdNum)" /> <xsl:variable name="ForeignTabla" select="$Tablas[@Id = current()/Tabla/@Id]" /> <xsl:if test="$ForeignTabla"> <Join> <xsl:value-of select="$Id" />.<xsl:value-of select="Col" /> <xsl:text> = </xsl:text> <xsl:value-of select="concat('T', $ForeignTabla/@IdNum)" /> <xsl:text>.</xsl:text> <xsl:value-of select="Col/@FK" /> </Join> </xsl:if> </xsl:for-each> > 4) NB. Is posible that in my input an intermediate table,necesary > for the JOIN (with Foreing's Keys) don't exits. I'm afraid I don't understand what this means. If you give an example, it might be clearer, but hopefully the above has given you enough ideas about how to solve your problem to let you do it yourself. I hope that helps, Jeni --- Jeni Tennison http://www.jenitennison.com/ XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] Problem solved, John Ericson | Thread | [xsl] Sorting an unsorted list of (, TSchutzerWeissmann |
[xsl] Jeni Tennisons XSLT/XPATH Boo, cutlass | Date | [xsl] Sorting an unsorted list of (, TSchutzerWeissmann |
Month |