Importing complex XML data from PubMed/Medline

[sgabrown]

I am trying to import PubMed XML data that is obtained by a http request for example:

http://eutils.ncbi.n...088&retmode=xml

... and have had some success for simple data with an XSL file copied at the end of this post. This is used on a solution posted some years back on this forum by Fenton Jones (http://fmforums.com/...-filemaker-pro/)

My problem arrises with repeating fields, specifically the author list.

I need to convert

*********************

<AuthorList>

<Author>

<LastName>Brown</LastName>

<ForeName>Simon G A</ForeName>

<Initials>SG</Initials>

</Author>

<Author>

<LastName>Wiese</LastName>

<ForeName>Michael D</ForeName>

<Initials>MD</Initials>

</Author>

<Author>

<LastName>van Eeden</LastName>

<ForeName>Pauline</ForeName>

<Initials>P</Initials>

</Author>

*********************

Into a single column as:

Brown SG, Wiese SG, van Eeden P.

For any given reference there may be any number of authors from 1 to 25, but this just needs to go into one field as a single line.

I am completely new to XML and must admit to nit understanding the structure very well. I can design fairly simple/functional databases using Filemaker for our research use, but I am not a programmer!

Any help would be greatly appreciated, and if I manage to solve the problem with help I will post the final XSL solution on the forum.

Thanks,

Simon

*******XLS CODE THAT WORKS WITH SIMPLE DATA*****

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT BUILD="" NAME="" VERSION=""/>

<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a"/>

<METADATA>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="PMID" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="DateCreated" TYPE="DATE"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Journal" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Title" TYPE="TEXT"/>

</METADATA>

<RESULTSET FOUND="{count(PubmedArticleSet/PubmedArticle)}">

<xsl:for-each select="PubmedArticleSet/PubmedArticle">

<ROW>

<xsl:attribute name="MODID">0</xsl:attribute>

<xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>

<COL>

<DATA>

<xsl:value-of select="MedlineCitation/PMID"/>

</DATA>

</COL>

<xsl:variable name="m" select="MedlineCitation/DateCreated/Month" />

<xsl:variable name="d" select="MedlineCitation/DateCreated/Day" />

<xsl:variable name="y" select="MedlineCitation/DateCreated/Year" />

<xsl:variable name="FM_date" select="concat($m,'/',$d, '/',$y)"/>

<COL>

<DATA>

<xsl:value-of select="$FM_date"/>

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="MedlineCitation/Article/Journal/Title"/>

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="MedlineCitation/Article/ArticleTitle"/>

</DATA>

</COL>

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

[comment]

For any given reference there may be any number of authors from 1 to 25, but this just needs to go into one field as a single line.

You need to loop over the authors and place all of their names in a new COL's DATA element:

<COL><DATA>

<xsl:for-each select="MedlineCitation/Article/AuthorList/Author">

<xsl:value-of select="LastName"/>

<xsl:value-of select="' '"/>

<xsl:value-of select="Initials"/>

<xsl:if test="position()!=last()">

<xsl:value-of select="', '"/>

</xsl:if>

</xsl:for-each>

</DATA></COL>

Don't forget to also add an Authors field to the METADATA section.

[nileshp789]

If you are new to xml you might want to think about using an xml tool like an xml editor, which would help you validate your xml and setup your structure correctly, child elements etc etc there’s plenty of free ones about, so it shouldn’t cost you anything.

[beverly]

If you are new to xml you might want to think about using an xml tool like an xml editor, which would help you validate your xml and setup your structure correctly, child elements etc etc there’s plenty of free ones about, so it shouldn’t cost you anything.

Could you please list the editors you have used and opinions about them?