Help with import XML

[Devin]

I'm trying to import an XML with the XSL, but it's not working. 

I'm not exactly sure what my issue is.. let alone if what I'm trying to do can even work with style of XML.

Currently I've been using xpath to loop thru and get all the records but due to the large number of records this can take a very long time

I've attached the example xml and xsl.

 

 

 

Inventory.xsl Inventory.xml

[comment]

AFAICT, your XML contains two records and each record has a list of fields. Assuming that each record has the same list of fields, in the same order, and that you want to import all of them, you could do simply:

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

<xsl:template match="/Envelope">
   <xsl:variable name="records" select="Body/loadValueObjectsResponse/out/valueObjects/ValueObject/children/ValueObjectsGroup/valueObjects/ValueObject"/>
   <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <METADATA>
         <!-- generate a list of fields from the first record -->
         <xsl:for-each select="$records[1]/fields/ValueField">
            <FIELD NAME="{name}" />
         </xsl:for-each>
      </METADATA>
      <RESULTSET>
         <xsl:for-each select="$records">
            <!-- create a record -->
            <ROW>
               <!-- populate the fields -->
               <xsl:for-each select="fields/ValueField">
                  <COL>
                     <DATA>
                        <xsl:value-of select="value"/>
                     </DATA>
                  </COL>
               </xsl:for-each>
            </ROW>
         </xsl:for-each>
      </RESULTSET>
   </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

A few notes:

• XML is case-sensitive; row is not the same thing as ROW ;
• "value" is not a valid value for field type; OTOH, you don't really need to specify the field type if you're importing into an existing table, where the fields are already defined.
• Some of the ValueField elements in the source XML do not have a value element; these will be imported as empty. You can exclude them from being imported by not including them in your import field order, or modify the stylesheet to process only ValueField[value]  nodes.

 

 

[Devin]

Interesting approach. I tested this and it works. 

Is their away to import this for every record?

Found at the bottom of XML.

<objectName>JobProject</objectName>
<primaryKey>5915</primaryKey>

 

The <objectName> is the  Filed Name and the <primaryKey> is the Vaule

[comment]

5 minutes ago, Devin said:

interesting approach

You can call it that. I call it "lazy" (in the good sense of the word).

 

9 minutes ago, Devin said:

Found at the bottom of XML.

<objectName>JobProject</objectName>
<primaryKey>5915</primaryKey>

It's not exactly at the bottom of the XML. It's at the bottom of the ValueObject element, which in itself is a child of valueObjects  - so this raises the question: can there be more than one ValueObject element? 

 

[Devin]

You are correct. Should have said near the bottom. 

But to answer you question. No there will not be another ValueObject with these.

 

[comment]

20 hours ago, Devin said:

No there will not be another ValueObject

Then you can do: 

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

<xsl:template match="/Envelope">
   <xsl:variable name="parent" select="Body/loadValueObjectsResponse/out/valueObjects/ValueObject"/>
   <xsl:variable name="records" select="$parent/children/ValueObjectsGroup/valueObjects/ValueObject"/>
   <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <METADATA>
         <!-- parent fields -->
         <FIELD NAME="objectName"/>
         <FIELD NAME="primaryKey"/>
         <!-- generate a list of fields from the first record -->
         <xsl:for-each select="$records[1]/fields/ValueField">
            <FIELD NAME="{name}" />
         </xsl:for-each>
      </METADATA>
      <RESULTSET>
         <xsl:for-each select="$records">
            <!-- create a record -->
            <ROW>
               <!-- populate the parent fields -->
               <COL>
                  <DATA>
                     <xsl:value-of select="$parent/objectName"/>
                  </DATA>
               </COL>
               <COL>
                  <DATA>
                     <xsl:value-of select="$parent/primaryKey"/>
                  </DATA>
               </COL>
               <!-- populate the other fields -->
               <xsl:for-each select="fields/ValueField">
                  <COL>
                     <DATA>
                        <xsl:value-of select="value"/>
                     </DATA>
                  </COL>
               </xsl:for-each>
            </ROW>
         </xsl:for-each>
      </RESULTSET>
   </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

 

Edited August 13, 2021 by comment