Jump to content

XML Import: Transforming XML Data with XSLT


Paul Webb

This topic is 3057 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

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

 

XSL

 
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2.   
  3.   
  4. <xsl:stylesheet  
  5.     version="1.0"  
  6.     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  
  7.     xmlns:fmp="http://www.filemaker.com/fmpxmlresult"  
  8.     exclude-result-prefixes="xsl fmp">  
  9.   
  10.   
  11.   <xsl:output  
  12.       method="xml"  
  13.       version="1.0"  
  14.       encoding="UTF-8"  
  15.       indent="yes">  
  16.   </xsl:output>  
  17.   
  18.   
  19.   <xsl:template match="/">  
  20.       
  21.     <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">  
  22.       <ERRORCODE>0</ERRORCODE>  
  23.       <PRODUCT BUILD="10-18-2015" NAME="FileMaker" VERSION="ProAdvanced 14"/>  
  24.       <DATABASE  
  25.           DATEFORMAT="m.d.yyyy"  
  26.           LAYOUT=""  
  27.           NAME=""  
  28.           RECORDS="{count(/*/*)}"  
  29.           TIMEFORMAT="k:mm:ss "/>  
  30.       <METADATA>  
  31.         <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="BTK-security-complete" TYPE="TEXT"/>  
  32.       </METADATA>  
  33.       <RESULTSET FOUND="">  
  34.   
  35.   
  36.         <xsl:for-each select="/*/*">  
  37.           <ROW MODID="0" xmlns="http://www.filemaker.com/fmpxmlresult">  
  38.             <xsl:attribute name="RECORDID">  
  39.               <xsl:value-of select="position()" />  
  40.             </xsl:attribute>  
  41.             <COL>  
  42.               <DATA>  
  43.                 <xsl:value-of select="//Field[@name='BTK-security-complete']" />  
  44.               </DATA>  
  45.             </COL>  
  46.           </ROW>  
  47.         </xsl:for-each>  
  48.   
  49.   
  50.       </RESULTSET>  
  51.     </FMPXMLRESULT>  
  52.   </xsl:template>  
  53. </xsl:stylesheet>  

 

Transformed XML

 
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2. <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">  
  3.    <ERRORCODE>0</ERRORCODE>  
  4.    <PRODUCT VERSION="ProAdvanced 14" NAME="FileMaker" BUILD="10-18-2015" />  
  5.    <DATABASE TIMEFORMAT="h:mm:ss " RECORDS="1" NAME="" LAYOUT="" DATEFORMAT="m.d.yyyy" />  
  6.    <METADATA>  
  7.       <FIELD TYPE="TEXT" NAME="BTK-security-complete" MAXREPEAT="1" EMPTYOK="YES" />  
  8.    </METADATA>  
  9.    <RESULTSET FOUND="">  
  10.       <ROW MODID="0" RECORDID="1">  
  11.          <COL>  
  12.             <DATA />  
  13.          </COL>  
  14.       </ROW>  
  15.    </RESULTSET>  
  16. </FMPXMLRESULT>  
Link to comment
Share on other sites

The reason why your attempt fails is this part in your input XML:

xmlns="cdetsng"

This places all the elements in your XML in a namespace. In order to address nodes in a namespace, you must declare the namespace in your stylesheet, assign it a prefix and use that prefix when referencing the nodes.

Try the following stylesheet:

<xsl:stylesheet  version="1.0"  
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  
xmlns:cde="cdetsng">  
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/cde:CDETS">  
  <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <METADATA>
      <FIELD NAME="BTK-security-complete" TYPE="TEXT"/>
    </METADATA>
    <RESULTSET>  
      <xsl:for-each select="cde:Defect">  
        <ROW>  
          <COL>
            <DATA>
              <xsl:value-of select="cde:Field[@name='BTK-security-complete']" />
            </DATA>
          </COL>  
        </ROW>  
      </xsl:for-each>  
    </RESULTSET>
  </FMPXMLRESULT>  
</xsl:template> 

</xsl:stylesheet>  

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

comment is so eloquent in the description of 'namespace'. :cool:

the namespace is helpful in keeping elements named the same from being confused by the transformation. for example, IF your source XML had the 'ROW' or 'DATA' element, they would need to be "unique" from the result XML (as in FMPXMLRESULT grammar) elements or 'ROW' and 'DATA' elements, as well.

and namespaces are also used to help validate an XML source schema

Link to comment
Share on other sites

This topic is 3057 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.