Re: Malformed (??) XML and XML 2 SQL XSLT transformati

<?xml version="1.0" encoding="iso8859-1" ?>
<Orders>
        <Customer cust-id="C6272">
                <Template Template-id="T37141">
                        <Fields FieldName="User Login ID">
                                <FieldValue>roger.moore</FieldValue>
                        </Fields>
                        <Fields FieldName="External Client ID">
                                <FieldValue>105</FieldValue>
                        </Fields>
                        <Fields FieldName="Job Docket">
                                <FieldValue>P377609</FieldValue>
                        </Fields>
                        <Fields FieldName="Order Date">
                                <FieldValue>10/14/2003</FieldValue>
                        </Fields>
                        <Fields FieldName="Order">
                                <FieldValue>O1084281</FieldValue>
                        </Fields>
                        <Fields FieldName="User Login ID">
                                <FieldValue>bernard</FieldValue>
                        </Fields>
                        <Fields FieldName="External Client ID">
                                <FieldValue>104</FieldValue>
                        </Fields>
                        <Fields FieldName="Job Docket">
                                <FieldValue>P377610</FieldValue>
                        </Fields>
                        <Fields FieldName="Order Date">
                                <FieldValue>10/16/2003</FieldValue>
                        </Fields>
                        <Fields FieldName="Order">
                                <FieldValue>O1084283</FieldValue>
                        </Fields>
                </Template>
        </Customer>
</Orders>

from what I want to generate :

INSERT INTO orders("User Login ID","External Client ID","Job Docket","Order Date","Order") values("roger.moore","105","P377609","10/14/2003","O1084281");
INSERT INTO orders("User Login ID","External Client ID","Job Docket","Order Date","Order") values("bernard","104","P377610","10/16/2003","O1084283");

with a XSLT transformer stylesheet.

This seems to be a sort of grouping problem. It would have been much easier
if the groups where already marked up in the source like
  <Template Template-id="T37141">
    <Record>
      <Fields FieldName="User Login ID">
        <FieldValue>roger.moore</FieldValue>
      </Fields>
      <Fields FieldName="External Client ID">
        ...81
      </Fields>
    </Record>
    <Record>
       <Fields FieldName="User Login ID">
...

Well, the structure is not too bad as it is. One somewhat generic approach
is to select the Fields with the same  as the first field and then select the
fields between them in an inner for-each. Unfortunately, I can't remember the
most elegant way to do the latter, but it is surely in the list archive:
  <xsl:template match="Template">
    <xsl:variable name="first-field-name" select="Fields[1]/@FieldName"/>
    <xsl:for-each select="Fields[FieldName=$first-field-name]">
      <xsl:variable name="this-id" select="generate-id()"/>
      <xsl:variable name="fields" select="following-sibling::Fields
       [generate-id(previous-sibling::Fields[1])=$this-id]"/>
      <xsl:text>INSERT INTO orders(</xsl:text>
      <xsl:for-each select="$fields">
        <xsl:text>"</xsl:text>
        <xsl:value-of select="@FieldName"/>
        <xsl:text>"</xsl:text>
        <xsl:if test="position()!=last()">
          <xsl:text>,</xsl:text>
        </xsl:if>
      </xsl:for-each>
      <xsl:text>)&#xA;values(</xsl:text>
      <xsl:for-each select="$fields">
        <xsl:text>"</xsl:text>
        <xsl:value-of select="FieldValue"/>
        <xsl:text>"</xsl:text>
        <xsl:if test="position()!=last()">
          <xsl:text>,</xsl:text>
        </xsl:if>
      </xsl:for-each>
      <xsl:text>);&#xA;</xsl:text>
    </xsl:for-each>
  </xsl:template>
Beware: untested.