How can I insert multiple files contents into xml?

Question

I have an xml file that provides input to a Workday integration. The input is an xml file, normally opened in Excel and populated by pasting the output of a SQL query. This particular xml, however, needs to be populated with data which would exceed Excel cell size limitations. The cell data is base64-encoded .pdf files. Each cell in one column would contain the contents of one .pdf file. I am able to create the xml where the File Content cells are marked with a short text string ("File Content"), but I need some way of replacing those markers with the encoded file content. I am sure there must be tools for this, but I am relatively new to xml manipulation. Possibilities that have occurred to me are Powershell, or xslt or one of the Oxygen apps, but I don't know which one would be best (XML Editor? Author ?). Attached are images of the xml opened in Excel and Oxygen.

XSLT 2 or 3 which you can execute inside of oXygen using the commercial editions of Saxon 9 or 10 together with the EXPath modules (e.g. https://www.saxonica.com/html/documentation/functions/expath-file/read-binary.html) should be able to transform such a document containing a reference to a PDF to one containing the base64 encoded file contents. — Martin Honnen, Apr 07 '21 at 06:19

score 0 · Answer 1 · answered Apr 07 '21 at 07:30

XSLT 3 with EXPath file module and Saxon EE and streaming would be somehow along the lines of

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:file="http://expath.org/ns/file"
    xpath-default-namespace="put excel namespace here"
    exclude-result-prefixes="#all"
    version="3.0">

    <!-- adjust these integers to match the cell index of the real input -->
    <xsl:param name="cell-index-file-name" as="xs:integer" select="3"/>
    <xsl:param name="cell-index-file-contents" as="xs:integer" select="6"/>
    
    <xsl:mode streamable="yes" on-no-match="shallow-copy" use-accumulators="#all"/>
    
    <xsl:accumulator name="pos" as="xs:integer?" initial-value="()" streamable="yes">
        <xsl:accumulator-rule match="Table" select="()"/>
        <xsl:accumulator-rule match="Table/Row" select="0"/>
        <xsl:accumulator-rule match="Table/Row/Cell" select="$value + 1"/>
    </xsl:accumulator>
    
    <xsl:accumulator name="file-name" as="xs:string?" initial-value="()" streamable="yes">
        <xsl:accumulator-rule match="Row/Cell[accumulator-before('pos') eq $cell-index-file-name]/text()" select="data()"/>
    </xsl:accumulator>
    
    <xsl:template match="Row/Cell[accumulator-before('pos') eq $cell-index-file-contents]">
        <xsl:copy>
            <xsl:value-of select="file:read-binary(resolve-uri(accumulator-before('file-name')))"/>
        </xsl:copy>
    </xsl:template>
    
</xsl:stylesheet>

Thank You Martin for the recommendation. I did install Java and download Saxon Enterprise Edition and experimented a bit. This was educational for me but for various reasons including the learning curve I would have had, I used more familiar tools to overcome the problem. I used PeopleCode in an App.Engine to generate the Excel "rows" block of xml. I used PeopleSoft delivered function GetBase64StringFromBinary() to encode the file content. — Chris, Apr 22 '21 at 14:46

How can I insert multiple files contents into xml?

1 Answers1