XML Import: Transforming XML Data with XSLT

[Paul Webb]

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>  

[comment]

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 October 21, 2015 by comment

[Paul Webb]

That did it! I don't quiet get the namespace portion of XML. Guess it's back to YouTube for me. Thanks for setting me straight!

[comment]

I don't quiet get the namespace portion of XML.

Think of it like a family name that an element has, in addition to a given name. An element like that will not respond if you call it by its first name only.

[beverly]

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

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