XML import from form service

[AndrewDavis]

Hello,

 

I have XML data from an online form service (below). How can I get the data into fields in FM?  Each record in my FM contains fields for each xml_serializer_tag/value. Also, is there a way to use a ‘for-each’ loop to create the field names in the meta data section (rather than manually describing each)? There is MUCH more data in the actual XML.

 

I was able to create an XSLT that transforms the info. But it was all getting dumped into one field, rather than each into their own.

 

Thanks for your help.

 

XML

<?xml version="1.0" encoding="utf-8"?>
<response status="ok">
	<submissions>
		<submission>
			<id>179890809</id>
			<timestamp>2015-01-21 07:44:55</timestamp>
			<data>
				<XML_Serializer_Tag>
					<field>29367523</field>
					<label>Email</label>
					<value>[email protected]</value>
				</XML_Serializer_Tag>
				<XML_Serializer_Tag>
					<field>29367524</field>
					<label>Name</label>
					<value>Fry</value>
				</XML_Serializer_Tag>
			</data>
		</submission>
		<submission>
			<id>179988171</id>
			<timestamp>2015-01-27 10:11:54</timestamp>
			<data>
				<XML_Serializer_Tag>
					<field>29367523</field>
					<label>Email</label>
					<value>[email protected]</value>
				</XML_Serializer_Tag>
				<XML_Serializer_Tag>
					<field>29367524</field>
					<label>Name</label>
					<value>Leela</value>
				</XML_Serializer_Tag>
			</data>
		</submission>
		<submission>
			<id>17998369</id>
			<timestamp>2015-02-17 11:15:24</timestamp>
			<data>
				<XML_Serializer_Tag>
					<field>29367523</field>
					<label>Email</label>
					<value>[email protected]</value>
				</XML_Serializer_Tag>
				<XML_Serializer_Tag>
					<field>29367524</field>
					<label>Name</label>
					<value>Bender</value>
				</XML_Serializer_Tag>
			</data>
		</submission>
	</submissions>
</response>

XSLT attempt (zero errors, but not getting the data into fields the way I would like)

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/">
		<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
			<METADATA>
				<FIELD NAME="Email" TYPE="TEXT"/>
				<FIELD NAME="Name" TYPE="TEXT"/>
			</METADATA>
			<RESULTSET FOUND="">
				<xsl:for-each select="//submission">
				<ROW MODID="" RECORDID="">
					<COL>
						<DATA><xsl:value-of select="data/XML_Serializer_Tag/value"/></DATA>
					</COL>
				</xsl:for-each>
			</RESULTSET>
		</FMPXMLRESULT>
	</xsl:template>
</xsl:stylesheet>

[comment]

XSLT attempt (zero errors

 

No, that's not possible. Your XSLT is missing a closing </ROW> tag and it will most certainly produce an error.

 

Once you fix that, you can address the other issue: your METADATA section declares two fields - but you are only creating one COL for each ROW.  So the Name field will always end up empty.

 

I'd suggest you try it this way:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/response">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <METADATA>
        <FIELD NAME="Email" TYPE="TEXT"/>
        <FIELD NAME="Name" TYPE="TEXT"/>
      </METADATA>
      <RESULTSET>
        <xsl:for-each select="submissions/submission">
          <ROW>
            <xsl:for-each select="data/XML_Serializer_Tag">
              <COL>
                <DATA><xsl:value-of select="value"/></DATA>
              </COL>
            </xsl:for-each>
          </ROW> 
        </xsl:for-each>
      </RESULTSET>
    </FMPXMLRESULT>
  </xsl:template>
</xsl:stylesheet>

This is assuming every <submission> in the source XML document contains exactly the two <XML_Serializer_Tag> tags, in the same order.

[AndrewDavis]

Thanks so much for your help! The missing ROW tag was a copy/paste error.

 

Unfortunately, the source XML has a variable amount of <XML_Serializer_Tag> tags inside each submission. Is there a way to accommodate for that?

[comment]

Unfortunately, the source XML has a variable amount of <XML_Serializer_Tag> tags inside each submission. Is there a way to accommodate for that?

 

Yes, you can call them by their label:

        ...
          <ROW>
            <COL>
              <DATA><xsl:value-of select="data/XML_Serializer_Tag[label='Email']/value"/></DATA>
            </COL>
            <COL>
              <DATA><xsl:value-of select="data/XML_Serializer_Tag[label='Name']/value"/></DATA>
            </COL>
          </ROW>
        ...

[AndrewDavis]

Thanks!

Is it possible to create the field names using something like this? First I got a 'need to escape bracket' error, then a 'whitespace' error. Just brainstorming here.

<METADATA>
 <xsl:for-each select="submissions/submission/data/XML_Serializer_Tag">  
  <FIELD NAME="&lt;xsl:value-of select="label"/&gt;" TYPE="TEXT"/> 
</xsl:for-each></METADATA>

[comment]

Is it possible to create the field names using something like this?

 

It might be possible, but I am at loss here because I don't see your entire document. The way you're trying to do this:

<xsl:for-each select="submissions/submission/data/XML_Serializer_Tag">

would create a FIELD for every XML_Serializer_Tag element in your document, leading to many duplicate FIELDs being created.

 

Usually, you would create a FIELD for every XML_Serializer_Tag element in the first submission:

<xsl:for-each select="submissions/submission[1]/data/XML_Serializer_Tag">

but that could not work with what you said earlier:

 

the source XML has a variable amount of <XML_Serializer_Tag> tags inside each submission.

 

So the question at this point is more about "what" then "how".

 

 

 

 

First I got a 'need to escape bracket' error, then a 'whitespace' error.

 

No, what you need to do is learn how to add an attribute to an element. Here's one way:

<FIELD TYPE="TEXT"/>
  <xsl:attribute name="NAME">
    <xsl:value-of select="label"/>
  </xsl:attribute>
</FIELD>

and here's another:

<FIELD NAME="{label}" TYPE="TEXT"/>