AnsweredAssumed Answered

XML Import: Transforming XML Data with XSLT

Question asked by PaulWebb on Oct 21, 2015
Latest reply on Oct 21, 2015 by beverly

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>

Outcomes