XML import issues, first

Marky · February 17, 2021

Dear helpers,

I managed to write an XSLT for an export job out of FMP a few years ago, thanks to Beverley's book and a lot of patience. It's been working without fail ever since!

Now I need to import an XML file in to FMP 18 with FMPXMLRESULT, using Oxygen 9 to test the XSL product.

I have multiple issues. I would like to start with the seemingly simplest.

In an example of xml

<a>

<b>

<c>

</c>

</b>

</a>

I feel I should be able to navigate to my node of interest by writing eg

<xsl:for-each select="a/b/c/c2">

but this does not work. I have to write

<xsl:for-each select="/*/*/*/*[position()=2]">

This functions, but is cumbersome and difficult to reconstruct. Additionally, it makes me wonder whether other troubles down the line are in connection with this.

There are many examples of FMPXMLRESULT on the net where the first notation works.

What am I doing wrong?

Your help is much appreciated.

Marky

ggt667 · February 17, 2021

I do not know oxygen 9, got a link? Do you reference one of these? https://www.oxygenxml.com/download.html

This is how to test the xslt of yours on the MacOS X command line

xsltproc html2fmpxmlresult.xslt theHTML.html

You can also write

xmlstarlet el theHTML.html

to list the possible XPath / node references

foreach requires a context however and is useless in solitude to me as it does not serve as a good example. Ffor these type of translations template is usually the way to go.

Edited February 18, 2021 by ggt667

ggt667 · February 17, 2021

2 hours ago, Marky said:

There are many examples of FMPXMLRESULT on the net where the first notation works.

What am I doing wrong?

You did not show us your XSLT, hence it's impossible to help you.

Edited February 17, 2021 by ggt667

comment · February 17, 2021

If:

<xsl:for-each select="a/b/c/c2">

doesn't work, but:

<xsl:for-each select="/*/*/*/*[position()=2]">

does, then your actual XML is different from what you show us here. Most likely, some or all of the elements are in a namespace.

Please post a real, full example of the input XML (replace any sensitive data with meaningless text), and indicate exactly what you want to import to where.

Marky · February 17, 2021

Dear helpers ggt667 and comment,

Thank you for your response.

Here my work up to now.

I get an xml statement with 1 or more payment references (included between <TxDtls> and </TxDtls>

I manage to import 1 reference into FMP, but not >1. This is because I cannot make the xsl loop and create multiple ROWS and therefore records.

Thanks for your help

Markymsdso_elem6.2.xslt 2020-12-15.xml

comment · February 17, 2021

As I suspected, your XML puts all its elements in a namespace. You must declare this namespace in your stylesheet, assign it a prefix and use this prefix when addressing the elements.

You didn't answer my query regarding what exactly you want to import. The following stylesheet will create a record for each TxDtls, with fields for AcctSvcrRef, Currency and Amount. You can add more fields if necessary.

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"
exclude-result-prefixes="ns1">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/ns1:Document">
	<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
		<METADATA>
			<FIELD NAME="AcctSvcrRef"/>
			<FIELD NAME="Currency"/>
			<FIELD NAME="Amount" TYPE="NUMBER"/>
			<!-- add more fields, if neccessary -->
		</METADATA>
		<RESULTSET>
			<xsl:for-each select="ns1:BkToCstmrDbtCdtNtfctn/ns1:Ntfctn/ns1:Ntry/ns1:NtryDtls/ns1:TxDtls">
				<ROW>
					<COL><DATA><xsl:value-of select="ns1:Refs/ns1:AcctSvcrRef"/></DATA></COL>
					<COL><DATA><xsl:value-of select="ns1:Amt/@Ccy"/></DATA></COL>
					<COL><DATA><xsl:value-of select="ns1:Amt"/></DATA></COL>
					<!-- add more columns, if neccessary -->
				</ROW>
			</xsl:for-each>
		</RESULTSET>
	</FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

ggt667 · February 17, 2021

This is what you already have compiles to

% xsltproc msdso_elem6.2.xslt 2020-12-15.xml | tidy -i -xml -wrap 0
No warnings or errors were found.

<?xml version="1.0" encoding="utf-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
  <ERRORCODE>0</ERRORCODE>
  <PRODUCT BUILD="" NAME="" VERSION="" />
  <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="1" TIMEFORMAT="h:mm:ss a" />
  <METADATA>
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Refs" TYPE="TEXT" />
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="AcctSvcrRef" TYPE="TEXT" />
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="EndToEndId" TYPE="TEXT" />
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Amt" TYPE="TEXT" />
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="AddtlTxInf" TYPE="TEXT" />
  </METADATA>
  <RESULTSET FOUND="1">
    <ROW MODID="0" RECORDID="0">
      <COL>
        <DATA>9930349VB0458806 EndToEndID2019ISO0000000142A101</DATA>
      </COL>
      <COL>
        <DATA>9930349VB0458806</DATA>
      </COL>
      <COL>
        <DATA>EndToEndID2019ISO0000000142A101</DATA>
      </COL>
      <COL>
        <DATA>400</DATA>
      </COL>
      <COL>
        <DATA>Incomings DB BESR Quick</DATA>
      </COL>
    </ROW>
  </RESULTSET>
</FMPXMLRESULT>

To learn more about HTML Tidy see http://tidy.sourceforge.net
Please send bug reports to [email protected]
HTML and CSS specifications are available from http://www.w3.org/
Lobby your company to join W3C, see http://www.w3.org/Consortium

I'm also waiting for your answer to what `comment` asked for.

Edited February 18, 2021 by ggt667

ggt667 · February 18, 2021

10 hours ago, comment said:

I believe you are better off using a template rather than foreach in this case. Comment's example translated to template below.


<xsl:stylesheet version="1.0" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"
                exclude-result-prefixes="ns1">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
  
<xsl:template match="/ns1:Document">
  <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <METADATA>
      <FIELD NAME="AcctSvcrRef"/>
      <FIELD NAME="Currency"/>
      <FIELD NAME="Amount" TYPE="NUMBER"/>
      <!-- add more fields, if neccessary -->
    </METADATA>
    <RESULTSET>
      <xsl:apply-templates select="ns1:BkToCstmrDbtCdtNtfctn/ns1:Ntfctn/ns1:Ntry/ns1:NtryDtls/ns1:TxDtls" />
    </RESULTSET>
  </FMPXMLRESULT>
</xsl:template>
<xsl:template match="ns1:BkToCstmrDbtCdtNtfctn/ns1:Ntfctn/ns1:Ntry/ns1:NtryDtls/ns1:TxDtls">
  <ROW>
    <COL><DATA><xsl:value-of select="ns1:Refs/ns1:AcctSvcrRef"/></DATA></COL>
    <COL><DATA><xsl:value-of select="ns1:Amt/@Ccy"/></DATA></COL>
    <COL><DATA><xsl:value-of select="ns1:Amt"/></DATA></COL>
    <!-- add more columns, if neccessary -->
  </ROW>
</xsl:template>
  
</xsl:stylesheet>

Edited February 18, 2021 by ggt667

comment · February 18, 2021

@ggt667

1. You are attributing me words I did not say.

2. There is absolutely no good reason for the change you made. It achieves nothing except making the code longer and harder to follow. Your claim that "you are better off using a template rather than foreach in this case" as well as the one you made earlier saying that "foreach requires a context however and should not really be used" are totally baseless and false. Moreover, this is not the first time you make such irresponsible claims with me having to refute them.

There should be kind some of sanction for repeatedly posting such nonsense.

Edited February 18, 2021 by comment

ggt667 · February 18, 2021

1) Please enlighten me: Which words or attributes are those?

2) Please explain. Do you ever run commands on the command line preceeded by `time`? Do you ever count clock cycles? Do you ever consider resources spent on your system? foreach is a resource intensive method compared to template. For the case of foreach and template it's also procedural vs declarative.

To this: "There should be kind some of sanction for repeatedly posting such nonsense." I totally agree. There is a fact that the ability to correct others is linearly inverse of the ability to correct oneself. I consider intelligence the ability to discuss a case without having to conclude.

22 minutes ago, comment said:

Your claim that "you are better off using a template rather than foreach in this case" as well as the one you made earlier saying that "foreach requires a context however and should not really be used" are totally baseless and false.

I have amended the sentence to make sense not only for its context above, but also alone for all eternity.

Edited February 18, 2021 by ggt667

comment · February 18, 2021

3 minutes ago, ggt667 said:

foreach is a resource intensive method compared to template.

No, it is not.

Do note that the results of any performance testing are valid only to the processor being tested. Filemaker does NOT use the same processor as the MacOS command line, so any results you get there are worthless - although I doubt even they support your claim.

ggt667 · February 18, 2021

I believe for the lack of nesting in the XSLT alone one should avoid foreach. The largest impact between the two is in the amount of memory spent during the translation, for the use case I tried when I made up my mind about this I had a 2 GB XML file that I needed to translate to FMPXMLRESULT for import into FileMaker, foreach took 2 hours 34 mins to convert while template for the same source file took 10 mins. This is not due to clock cycles alone, from what I gathered this was mainly due to how memory is managed for the 2 different approaches. And if you are going to convert 1-10 000 records you will hardly notice the difference.

Edited February 18, 2021 by ggt667

comment · February 18, 2021

I don't really care to discuss other cases which I cannot see.

FYI, the ONLY difference between xsl:for-each and xsl:apply-templates is that xsl:apply-templates says "take these nodes, find the best fitting template for each one them, and apply it", while xsl:for-each says "take these nodes and apply this template to them". Apparently you have read somewhere the totally made-up distinction propagated by some "experts" between procedural and declarative. There is no such thing. XSLT is a functional language - and xsl:for-each is an integral part of it.

ggt667 · February 18, 2021

You are right XSLT is a functional templating language for XML, and I never attempted to debunk that fact. Still it's possible to obtain higher legibility of your source code through different approaches; such as procedural or declarative styles of writing.

25 minutes ago, comment said:

There is no such thing. XSLT is a functional language - and xsl:for-each is an integral part of it.

In my experience xsl:for-each has its benefit where FileMaker would use value lists.

Edited February 18, 2021 by ggt667

Marky · February 18, 2021

Dear comment and ggt667,

Thank you very much for your input.

I am glad to learn about the namespace and the consequences it entails, it makes the whole code much more tangible.

Both your solutions work like a dream for the looping affair.

As far as I can see I now have one more difficulty and I'd be glad for your input, ggt667 and comment.

Looking at my xml file I sent you and as I commented in msdso_elem6.2.xslt, there is a section (from <NtryRef> to </BkTxCd>) which should appear in every ROW and is identical for every ROW. Now when I foreach or template <TxDtls>, I do not seem to be able to insert this information into the 3 records (of this example).

Here are my unsuccessful attempts.

Thanks again

Marky

template6.5.xsl foreach6.4.xslt

ggt667 · February 18, 2021

24 minutes ago, Marky said:

there is a section (from <NtryRef> to </BkTxCd>) which should appear in every ROW and is identical for every ROW.

Can you please write statically as examples what you have on side( in the XML file ) and what you would like it to look like after its translation?

comment · February 18, 2021

What you did makes no sense. If you want to create a record for each TxDtls, you MUST put the ROW element within the xsl:for-each instruction. Otherwise your stylesheet will create exactly one record.

Now, to add a field with a constant value to every record, you could reach out for the value using a relative path from the current TxDtls. However, that is inefficient. A better approach is to store the constant value in a variable once, and retrieve it from there:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"
exclude-result-prefixes="ns1">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/ns1:Document">
	<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
		<METADATA>
			<FIELD NAME="NtryRef" TYPE="NUMBER"/>
			<FIELD NAME="AcctSvcrRef"/>
			<FIELD NAME="Currency"/>
			<FIELD NAME="Amount" TYPE="NUMBER"/>
		</METADATA>
		<RESULTSET>
			<xsl:variable name="NtryRef" select="ns1:BkToCstmrDbtCdtNtfctn/ns1:Ntfctn/ns1:Ntry/ns1:NtryRef" />
			<xsl:for-each select="ns1:BkToCstmrDbtCdtNtfctn/ns1:Ntfctn/ns1:Ntry/ns1:NtryDtls/ns1:TxDtls">
				<ROW>
					<COL><DATA><xsl:value-of select="$NtryRef"/></DATA></COL>
					<COL><DATA><xsl:value-of select="ns1:Refs/ns1:AcctSvcrRef"/></DATA></COL>
					<COL><DATA><xsl:value-of select="ns1:Amt/@Ccy"/></DATA></COL>
					<COL><DATA><xsl:value-of select="ns1:Amt"/></DATA></COL>
				</ROW>
			</xsl:for-each>
		</RESULTSET>
	</FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Note that this assumes there is only one Ntry element in the input XML.

Marky · February 19, 2021

Dear ggt667 and comment,

The solution with variables function perfectly for me, thank you comment!

I need 3 items from outside TxDtls, ie

"ns1:Ntfctn/ns1:Id", "ns1:Ntfctn/ns1:Ntry/ns1:BookgDt/ns1:Dt" and "ns1:Ntfctn/ns1:Ntry/ns1:ValDt/ns1:Dt"

in every ROW

How would this be solved with templates, ggt667?

Thank you very much for your invaluable help, both of you!

Marky

foreach6.4.xslt

ggt667 · February 23, 2021

On 2/19/2021 at 7:43 PM, Marky said:

How would this be solved with templates, ggt667

That part would be exactly the same.

Marky · February 27, 2021

OK, thanks, I learnt a lot in a short time, with your help!

Marky

ggt667 · February 27, 2021

8 minutes ago, Marky said:

OK, thanks, I learnt a lot in a short time, with your help!

Marky

Just ask.

I have been doing XSLT since 1998, I read alot of source code libxslt amongst others in that time, and heaps of man pages, I did not read any books, so I have no formal education in this, I have tried, failed, and experienced. There is a book `XSLT Jumpstarter - Level the Learning Curve and Put Your XML to Work` that may or may not be good if you'd like to learn stuff "correctly" opposed to by need.

Marky · March 1, 2021

OK, thanks!

Sign In

XML import issues, first

Recommended Posts

Marky

ggt667

ggt667

comment

Marky

comment

ggt667

ggt667

comment

ggt667

comment

ggt667

comment

ggt667

Marky

ggt667

comment

Marky

ggt667

Marky

ggt667

Marky

Create an account or sign in to comment

Create an account

Sign in

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information