Q:

XSLT Merge two XML via condition join / non conditional join

I am hoping this is very easy. I have two XML structure in the same file.
<?xml version="1.0" encoding="UTF-8"?>
<DBS>
    <DB>
        <DBNAME>DB1</DBNAME>
        <LOCAL>US</LOCAL>
    </DB>
    <DB>
        <DBNAME>DB2</DBNAME>
        <LOCAL>CN</LOCAL>
    </DB>
</DBS>
<SCRIPTS>
    <Script>
        <ID>1</ID>
        <SPTEXT>TEST1</SPTEXT>
    </Script>
    <Script>
        <ID>2</ID>
        <SPTEXT>TEST2</SPTEXT>
    </Script>
</SCRIPTS>

How do I get it merged together. SO it would be something like
<?xml version="1.0" encoding="UTF-8"?>
<RUNSQS>
    <RUNSQ>
        <DBNAME>DB1</DBNAME>
        <LOCAL>US</LOCAL>
        <SCRIPT>TEST1</SCRIPT>
    </RUNSQ>
    <RUNSQ>
        <DBNAME>DB2</DBNAME>
        <LOCAL>CN</LOCAL>
        <SCRIPT>TEST1</SCRIPT>
    </RUNSQ>
    <RUNSQ>
        <DBNAME>DB1</DBNAME>
        <LOCAL>US</LOCAL>
        <SCRIPT>TEST2</SCRIPT>
    </RUNSQ>
    <RUNSQ>
        <DBNAME>DB2</DBNAME>
        <LOCAL>CN</LOCAL>
        <SCRIPT>TEST2</SCRIPT>
    </RUNSQ>
</RUNSQS>

I have tried to use for-each paracter through both set. But the problem is when I get to the one for-each. I coudln't reference the DBNAME with second foreach. Couldn't make a variable referencable either. 

A:

Your transformation won't work first of all because your XML file is not well formed. You have to fix that by either:
1) Separating the XML in two files:
dbs.xml
<?xml version="1.0" encoding="UTF-8"?>
<DBS>
    <DB> ... </DB>
    ...
</DBS>

scripts.xml
<?xml version="1.0" encoding="UTF-8"?>
<SCRIPTS>
    <Script>...</Script>
...
</SCRIPTS>

or
2) Adding a common root node:
<root>
    <DBS>...</DBS>
    <SCRIPTS>...</SCRIPTS>
</root>

I'm assuming, from your example, that you select the Script nodes from the SCRIPTS tree according to their relative position (which is kind of risky, actually). You could use different criteria (like ID compared to the suffix of DBNAME, for example). Anyway, I'm assuming the selection by relative position. You will have to adapt the code if you decide to test using other criteria.
1) For the case where you can place the XMLs in different files, you can load each one in a xsl:variable or xsl:param:
<xsl:param name="dbs" select="document('dbs.xml')" />
<xsl:param name="scripts" select="document('scripts.xml')" />

And use $dbs and $scripts as roots for selecting the nodes from each file. This stylesheet achieves what you want using this strategy:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:param name="dbs" select="document('dbs.xml')" />
    <xsl:param name="scripts" select="document('scripts.xml')" />

    <xsl:template match="/">
        <RUNSQS>           
            <xsl:apply-templates select="$dbs/DBS"/>
        </RUNSQS>
    </xsl:template>

    <xsl:template match="DB">
        <xsl:variable name="pos" select="position()" />
        <RUNSQ>
            <xsl:copy-of select="*"/>
            <xsl:apply-templates select="$scripts/SCRIPTS/Script[$pos]" />
        </RUNSQ>
    </xsl:template>

    <xsl:template match="Script">
        <SCRIPT><xsl:value-of select="SPTEXT"/></SCRIPT>
    </xsl:template>

</xsl:stylesheet>

(I used <xsl:strip-space> to avoid errors in node position() since I'm using that criterium to select the nodes in $scripts.)
If you can simply add a <root> to your file, making it one well-formed XML, then you can start matching from the root and from there select the other elements you need using absolute or relative XPath within the file:
<xsl:template match="root"> <!-- change this to match root -->
    <RUNSQS>           
        <xsl:apply-templates select="DBS"/>
    </RUNSQS>
</xsl:template>

<xsl:template match="DB">
    <xsl:variable name="pos" select="position()" />
    <RUNSQ>
        <xsl:copy-of select="*"/>
        <xsl:apply-templates select="../../SCRIPTS/Script[$pos]" /> <!-- this, or absolute XPath -->
    </RUNSQ>
</xsl:template>

