7 Replies Latest reply on Oct 21, 2015 12:10 PM by beverly

    XML Import: Transforming XML Data with XSLT

    PaulWebb

      I've spent the last couple of days learning XML, XPath, XSLT trying to make this work. I feel like I am really close but something simple is off. My XSLT is not quiet right and I need some expert input. For testing I set it up to only pull in the first field. Once I have that right the rest should be easy.

       

      Below I have added the XML, XSL, and Transformed XML. You can see in the transformer portion that there is something off with my data. When trying to import into FM it just creates a blank record. I am trying to import the "Y" from the "BTK-security-complete" field.

      XML

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <CDETS xmlns="cdetsng" xmlns:ns2="http://www.w3.org/1999/xlink">
          <Defect id="CSCsa8849" ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849">
              <Field name="BTK-security-complete">Y</Field>
              <Field name="Class">CSC.swtools</Field>
              <Field name="Component">other</Field>
              <Field name="DE-manager">bnado</Field>
              <Field name="Description">Lifecycle changes to Pre when a Obsolete concept moved under Pre Parent</Field>
              <Field name="Headline">Lifecycle changes to Pre when a Obsolete concept moved under Pre Parent</Field>
              <Field name="Identifier" ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849">CSCsa8849</Field>
              <Field name="Is-customer-visible">N</Field>
             <AuditTrail id="Tue Apr 19 12:27:53 PDT 2005" defectID="CSCsa8849" ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849/audittrail">
                  <Parent ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849">CSCsa8849</Parent>
                  <Field name="ChangedBy">ksahani</Field>
                  <Field name="ChangedOn">04/19/2005 12:27:53</Field>
                  <Field name="Field">Defect Created</Field>
                  <Field name="Operation">New Record</Field>
              </AuditTrail>
              <Files>
                  <File id="DDTS_History.txt" defectID="CSCsa8849" ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849/file/DDTS_History.txt">
                      <Parent ns2:href="http://cdetsng.mydomain.com/wsapi/bug/CSCsa8849">CSCsa8849</Parent>
                      <Field name="Extension">txt</Field>
                      <Field name="FileSize">55</Field>
                      <Field name="Filename">DDTS_History</Field>
                      <Field name="UpdatedBy">cdetsync</Field>
                      <Field name="UpdatedOn">01/22/2008 17:25:00</Field>
                  </File>
              </Files>
          </Defect>
      </CDETS>
      


      XSL

      <?xml version="1.0" encoding="UTF-8"?>
      
      
      <xsl:stylesheet
          version="1.0"
          xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
          xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
          exclude-result-prefixes="xsl fmp">
      
      
        <xsl:output
            method="xml"
            version="1.0"
            encoding="UTF-8"
            indent="yes">
        </xsl:output>
      
      
        <xsl:template match="/">
          
          <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
            <ERRORCODE>0</ERRORCODE>
            <PRODUCT BUILD="10-18-2015" NAME="FileMaker" VERSION="ProAdvanced 14"/>
            <DATABASE
                DATEFORMAT="m.d.yyyy"
                LAYOUT=""
                NAME=""
                RECORDS="{count(/*/*)}"
                TIMEFORMAT="k:mm:ss "/>
            <METADATA>
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="BTK-security-complete" TYPE="TEXT"/>
            </METADATA>
            <RESULTSET FOUND="">
      
      
              <xsl:for-each select="/*/*">
                <ROW MODID="0" xmlns="http://www.filemaker.com/fmpxmlresult">
                  <xsl:attribute name="RECORDID">
                    <xsl:value-of select="position()" />
                  </xsl:attribute>
                  <COL>
                    <DATA>
                      <xsl:value-of select="//Field[@name='BTK-security-complete']" />
                    </DATA>
                  </COL>
                </ROW>
              </xsl:for-each>
      
      
            </RESULTSET>
          </FMPXMLRESULT>
        </xsl:template>
      </xsl:stylesheet>
      
      
      
      
      
      
      
      
      
      
      
      

       

      Transformed XML

      <?xml version="1.0" encoding="UTF-8"?>
      <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
         <ERRORCODE>0</ERRORCODE>
         <PRODUCT VERSION="ProAdvanced 14" NAME="FileMaker" BUILD="10-18-2015" />
         <DATABASE TIMEFORMAT="h:mm:ss " RECORDS="1" NAME="" LAYOUT="" DATEFORMAT="m.d.yyyy" />
         <METADATA>
            <FIELD TYPE="TEXT" NAME="BTK-security-complete" MAXREPEAT="1" EMPTYOK="YES" />
         </METADATA>
         <RESULTSET FOUND="">
            <ROW MODID="0" RECORDID="1">
               <COL>
                  <DATA />
               </COL>
            </ROW>
         </RESULTSET>
      </FMPXMLRESULT>