XML Transformation using XSLT

Avatar

Avatar

tarekahf

Avatar

tarekahf

tarekahf

27-07-2019

We have a complex XDP for Appraiser Application Form with XML Data Model (default one, nothing fancy). There is another XDP of the same Application Form, same layout, but with different model and XML Structure. The XML Structure of the 2nd form is based on format which I am not familiar with, but I can understand what is in there.

The 1st XML is plain vanilla XML, straight forward.

The 2nd XML has multiple nested sections, and the field names are specified in the attribute of the tag element. The images are specified in the attachment section, and the are references from the relevant tag element.

Following is a sample of the 1st XML:

<app_rep1>

  <NEW_primaryform>Some-primary-form</NEW_primaryform>

  <NEW_TITLE_PHOTO>SamplePhotoLabel1</NEW_TITLE_PHOTO>

  <NEW_CITY>Toronto</NEW_CITY>

  <NEW_Stage>Stage Value</NEW_Stage>

  <NEW_GS_AGE>23</NEW_GS_AGE>

  <NEW_POOL_X>Nice Pool</NEW_POOL_X>

  <NEW_PHOTO1>/9j/4AAQSkZJRgABAAEASABIAAD//gAfTEVBRCBUZWNobm9sb2dpZXMgSW5jLiBWMS4wMQD/2wCE

bla...bla...bla

  </NEW_PHOTO1>

</app_rep1>

Following is a sample of the 2nd XML:

<app_rep2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.1">

  <appraisal>

    <configuration>

      <primaryform>Some-primary-form</primaryform>

      <versioninfo>

        <acifileversion>123</acifileversion>

        <collectionversion>1.2.3.4</collectionversion>

        <collection>Report Pro</collection>

        <platform>Desktop Platform</platform>

      </versioninfo>

    </configuration>

    <data>

      <form name="photo1" primary="false">

        <section type="other" number="0" name="section_name1">

          <tag name="TITLE_PHOTO">SamplePhotoLabel1</tag>

          <tag name="LINE_PHOTO">SamplePhotoDescription1</tag>

        </section>

        <tag name="PHOTO1">../../../attachments/attachment[@key='267463fa-0073-4c10-83d6-fd8141641b72']</tag>

      </form>

      <form name="Some-primary-form" primary="true">

        <section type="other" number="0" name="OPTIONS">

          <tag name="OPT_TYPE_OF_APPRAISAL">Appraisal Type Value</tag>

        </section>

        <section type="subject" number="0" name="SUBJECT">

          <tag name="CITY">Toronto</tag>

          <tag name="STATE">Stage Value</tag>

          <tag name="GS_AGE">23</tag>

          <tag name="POOL_X">Nice Pool</tag>

        </section>

     </form>

    </data>

    <attachments>

      <attachment type="photo" label="" key="267463fa-0073-4c10-83d6-fd8141641b72">

        <image>

          <binary format="jpeg">/9j/4AAQSkZJRgABAAEASABIAAD//gAfTEVBRCBUZWNobm9sb2dpZXMgSW5jLiBWMS4wMQD/2wCE

bla...bla...bla

          </binary>

        </image>

      </attachment>

    </attachments>

  </appraisal>

</app_rep2>

As you can see, I want to transform the 2nd XML to look like the 1st XML.

I think I need to first write the XSLT. Then, I need to import the 2nd XML into the 1st XDP, and apply the XSLT while importing.

Am I thinking in the right direction?

Can you please help achieve my goal? I am not exactly sure how to do that.

Questions:

1. How to develop the XSLT?

2. How to apply the XSLT while merging the 2nd XML with the 1st XDP? How I can do than using Acrobat? How I can do that using Adobe LiveCycle Process Management?

Any help would be appreciated.

Regards,

Tarek

Accepted Solutions (0)

Answers (8)

Answers (8)

Avatar

Avatar

_Bruce_Robertson

MVP

Total Posts

1.8K

Likes

268

Correct Answer

372

Avatar

_Bruce_Robertson

MVP

Total Posts

1.8K

Likes

268

Correct Answer

372
_Bruce_Robertson
MVP

29-07-2019

Hi Tarek,

It might not help, but wanted to point out that you could bind to the second sample of xml (an inverted xml) using a predicate expressions, so if you had a field called Photo Title, the binding expression would be;

$.appraisal.data.form.[name=="photo1"].section.tag.[name=="TITLE_PHOTO"]

and would resolve to the value "SamplePhotoLabel1"

Bruce

Avatar

Avatar

altrue990

Avatar

altrue990

altrue990

29-07-2019

Try dyn:evaluate()

xsl:stylesheet version="1.0"
 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 
xmlns:dyn="http://exslt.org/dynamic"
 
extension-element-prefixes="dyn">

 
<xsl:param name="path"/>

 
<xsl:output method="text"/>

 
<xsl:template match="/">
 
<xsl:value-of select="dyn:evaluate($path)"/>
 
</xsl:template>

</xsl:stylesheet>

Avatar

Avatar

tarekahf

Avatar

tarekahf

tarekahf

28-07-2019

I think i can manage to write XSLT to perform the transformation. However, I am facing the following difficulties:

1. The image fields are located in another element. I mean, you have to do another lookup to find the image Base64 data. For example, the image field in the XML above app_rep2/appraisal/form[@name="photo1"]/tag[@name="PHOTO1"] is pointing to "../../../attachments/attachment[@key='267463fa-0073-4c10-83d6-fd8141641b72']" which has the image data and possibly some other details. I was unable to use XSL to perform this operation. I found some libraries that can do that, see this for example:

https://groups.google.com/forum/#!topic/microsoft.public.xsl/IzFmBvnlRHw 

When I tried to use the library, it didn't work, I am not sure why. Also, I don't know if this is supported with Adobe XFA and Acrobat.

2. The form has more than 1000 fields. I am wondering if there is a tool or a method that will help to make this job easier. For example, I am thinking to have someone work on the mapping and prepare an Excel Sheet with field name mapping from XML 1 to XML 2. This is easy but time consuming. Then, we will write the XSL, and use some method to find/replace all fields based in the field mapping done in Excel Sheet. I can write a JavaScript program for this, but I am trying to avoid this route.

Tarek

Avatar

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485
radzmar
MVP

15-08-2019

To transform the second XML in the way of the first you'll need a stylesheet like this:

<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="appraisal">

<xsl:variable name="RootName" select="./parent::node()/local-name()"/>

<xsl:element name="{$RootName}">

<xsl:element name="NEW_primaryform">

<xsl:value-of select="./configuration/primaryform"/>

</xsl:element>

<xsl:element name="NEW_TITLE_PHOTO">

<xsl:value-of select="./data/form/section/tag[@name='TITLE_PHOTO']/text()"/>

</xsl:element>

<xsl:element name="NEW_CITY">

<xsl:value-of select="./data/form/section/tag[@name='CITY']/text()"/>

</xsl:element>

<xsl:element name="NEW_STAGE">

<xsl:value-of select="./data/form/section/tag[@name='STATE']/text()"/>

</xsl:element>

<xsl:element name="NEW_GS_AGE">

<xsl:value-of select="./data/form/section/tag[@name='GS_AGE']/text()"/>

</xsl:element>

<xsl:element name="NEW_POOL_X">

<xsl:value-of select="./data/form/section/tag[@name='POOL_X']/text()"/>

</xsl:element>

<!--Apostrophe character-->

<xsl:variable name="apos" select='"&apos;"'/>

<xsl:variable name="PhotoTags" select="./data/form/tag[contains(@name, 'PHOTO')]"/>

<xsl:variable name="Attachments" select="./attachments/attachment"/>

<!--Check all form elements tagged as photo-->

<xsl:for-each select="$PhotoTags">

<xsl:variable name="TagName" select="./@name"/>

<xsl:variable name="NewTagName" select="concat('NEW_', $TagName)"/>

<!--Extract the key of the photo-->

<xsl:variable name="Key" select="substring-before( substring-after( ./text(), $apos), $apos)"/>

<xsl:element name="{$NewTagName}">

<xsl:value-of select="$Attachments[@key=$Key]/image/binary/text()"/>

</xsl:element>

</xsl:for-each>

</xsl:element>

</xsl:template>

</xsl:stylesheet>

It extracts the key of a photo under the the <data>-section and that grabs the related binary data from the <attachments>-section.

The result looks this way.

<app_rep2>

<NEW_primaryform>Some-primary-form</NEW_primaryform>

<NEW_TITLE_PHOTO>SamplePhotoLabel1</NEW_TITLE_PHOTO>

<NEW_CITY>Toronto</NEW_CITY>

<NEW_STAGE>Stage Value</NEW_STAGE>

<NEW_GS_AGE>23</NEW_GS_AGE>

<NEW_POOL_X>Nice Pool</NEW_POOL_X>

<NEW_PHOTO1>/9j/4AAQSkZJRgABAAEASABIAAD//gAfTEVBRCBUZWNobm9sb2dpZXMgSW5jLiBWMS4wMQD/2wCE 

bla...bla...bla 

</NEW_PHOTO1>

</app_rep2> 

Hope this helps.

Avatar

Avatar

altrue990

Avatar

altrue990

altrue990

13-08-2019

The dyn is a vendor extension and only certain XSLT engines will support it. I'm not sure what engine the online transformation tool is using.

Avatar

Avatar

tarekahf

Avatar

tarekahf

tarekahf

06-08-2019

This will work, but when I tried to apply the above using this online tool for example, it didn't work:

Free Online XSL Transformer (XSLT) - FreeFormatter.com

Also, there s XSL plugin with Notepad++, but for some reason, I cannot use the dyn library.

I'm not sure what is wrong?!

Can you help with this?

Tarek

Avatar

Avatar

tarekahf

Avatar

tarekahf

tarekahf

06-08-2019

Thanks Bruce! There are more than 1000 fields, it'll be very hard to do such mapping. I am looking for a solution to automate this or at least introduce a step to minimize the manual work.

Steps I am thinking of:

1. Write XSL to map the structure and leave field names as is. Looks like this is doable.

2. Prepare a table in Excel Sheet to map the field names from 1st XML to the 2nd XML. It'll be simple two columns table.

3. Apply find/replace using the table rows in 2 above as input. I can write a program to perform this find/replace, but I am looking for a desktop tool to perform this find/replace. I couldn't find a way to do this using Notepad++ for example.

If you can help with the above, that would be great.

Tarek

Avatar

Avatar

altrue990

Avatar

altrue990

altrue990

28-07-2019

Yes, you can embed an XSLT into your XDP and have it run before the data is merged in the form. Keep in mind that Acrobat only supports XSLT 1.0, using a processor called Ginger Alliance.