CODEWITHSUNDEEP
xmlxsltxslt-1.0
sblandin
sblandin

Reputation: 964

Produce an xslt output in a specific order

I have a large xml document that looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<Data>
  <aTable>
    <aTableRow Col1="1" Col2="someText"/>
    <aTableRow Col1="2" Col2="newText"/>
    ...
  </aTable>
  <anotherTable>
    <anotherTableRow Col3="someText" Col4="42"/>
    <anotherTableRow Col3="myText" Col4="34"/>
    ...
  </anotherTable>
  ...
</Data>

I needed to transform the document in a corresponding sequence of SQL INSERT statement. Something like:

INSERT INTO aTable (Col1, Col2) VALUES (1, "someText")

I already have the transformation from a "table" to the corresponding SQL statement, but the xslt output is generated in the same order of the document.

Is there a way to have for example anotherTable before aTable?

EDIT

Thanks for the comments and answer. Reading them I realized that my original question wasn't that clear. I'll try to add more detail.

I have produced a template that correctly transform xml data in a sequence of insert statements suppose that this template is named "insertGeneration".

then i wrote something like:

<xsl:template match="Data/anotherTable">
  <xsl:call-template name="insertGeneration"/>
</xsl:template>

<xsl:template match="Data/aTable">
  <xsl:call-template name="insertGeneration"/>
</xsl:template>

I expected the output to be generated in this order, i.e. all INSERT INTO anotherTable before all INSERT INTO aTable. But the resulting order is the same of the source document.

I need a specific order otherwise I could violate foreign key constraints when executing SQL script.

My xslt processor is Visual Studio 2005. Well, not properly a compact xslt processor ;-)

Upvotes: 0

Views: 928

Answers (1)

Tomalak
Tomalak

Reputation: 338178

<xsl:output method="text" encoding="UTF-8" />

<xsl:template match="Data">
  <xsl:apply-templates select="*/*">
    <xsl:sort select="name(..)" case-order="lower-first" />
  </xsl:apply-templates>
</xsl:template>

<xsl:template match="Data/*/*">
  <xsl:text>INSERT INTO [</xsl:text>
  <xsl:value-of select="name(..)" />
  <xsl:text>] (</xsl:text>
  <xsl:apply-templates select="@*" mode="names" />
  <xsl:text>) VALUES (</xsl:text>
  <xsl:apply-templates select="@*" mode="values" />
  <xsl:text>)&#xA;</xsl:text>
</xsl:template>

<xsl:template match="Data/*/*/@*" mode="names">
  <xsl:text>[</xsl:text>
  <xsl:value-of select="name()" />
  <xsl:text>]</xsl:text>
  <xsl:if test="position() &lt; last()">, </xsl:if>
</xsl:template>

<xsl:template match="Data/*/*/@*" mode="values">
  <xsl:text>'</xsl:text>
  <xsl:call-template name="string-replace">
    <xsl:with-param name="search">'</xsl:with-param>
    <xsl:with-param name="replace">''</xsl:with-param>
  </xsl:call-template>
  <xsl:text>'</xsl:text>
  <xsl:if test="position() &lt; last()">, </xsl:if>
</xsl:template>

Which produces T-SQL compatible output, like this:

INSERT INTO [anotherTable] ([Col3], [Col4]) VALUES ('someText', '42')
INSERT INTO [anotherTable] ([Col3], [Col4]) VALUES ('myText', '34')
INSERT INTO [aTable] ([Col1], [Col2]) VALUES ('1', 'some''Text')
INSERT INTO [aTable] ([Col1], [Col2]) VALUES ('2', 'newText')

Tweak the code to your DB's syntax if necessary.

This uses lexicographical order by table name. If you want a different order, either change the <xsl:sort> accordingly or use several separate <xsl:apply-templates> to produce the wanted sequence. (Be aware, some XSLT processors seem to ignore the case-order parameter.)

Note that string-replace is a named template that implements a string replacement function (necessary for XSLT 1.0). If you have XSLT 2.0 you could simply use the built-in string-replace() function.

<xsl:template name="string-replace">
  <xsl:param name="subject" select="string()" />
  <xsl:param name="search" />
  <xsl:param name="replace" />

  <xsl:variable name="head" select="substring-before($subject, $search)" />
  <xsl:variable name="tail" select="substring-after($subject, $search)" />
  <xsl:variable name="found" select="$head or $tail" />

  <xsl:if test="not($found)">
    <xsl:value-of select="$subject" />
  </xsl:if>

  <xsl:if test="$found">
    <xsl:value-of select="$head" />
    <xsl:value-of select="$replace" />
    <xsl:call-template name="string-replace">
      <xsl:with-param name="subject" select="$tail" />
      <xsl:with-param name="search" select="$search" />
      <xsl:with-param name="replace" select="$replace" />
    </xsl:call-template>
  </xsl:if>
</xsl:template>

Upvotes: 1

Related Questions