Flow Flow - 4 months ago 17x
Java Question

XSLT read from two different sources (XML file and Excel sheet)

Is there a way I can make my

file get input from two different sources? One source is a XML file and the other is an Excel sheet. I need to get data from both of them. For example, I have this

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="yes"/>

<xsl:template match="Data/AAA">
<xsl:for-each select=".">
<Data xmlns="MyProtocol.xsd">
<Id><xsl:value-of select="Id"/></Id>
<Timestamp><xsl:value-of select="Timestamp"/></Timestamp>
<xsl:if test="Transfer">
<Transfer><xsl:value-of select="Transfer"/></Transfer>
<xsl:for-each select="Sequence/Number">
<Number><xsl:value-of select="."/></Number>


Excel Sheet:


1 Number | Code
2 5556667777 1
3 5559877890 1
4 5552835291 0
. ...
. ...
. ...

The xml:

<Data xmlns="MyProtocol.xsd">

From here, I want to change the
for the
<xsl:for-each select="Sequence/Number">
part. I would like to to the following:

  • Read the
    from the xml file

  • Check if that number is in the Excel sheet

  • Assign the correct
    number for that number in the

I have code where I can pass the XML file so that the xslt can make a new XML file according to the xslt; however, I can't figure out how to do the above.

Here's the code I have:

File stylesheet = new File(xsltFilePath);
InputSource inputSource = new InputSource(new ByteArrayInputStream(xmlString.getBytes()));

Document document = DocumentBuilderFactory.newInstance()

StreamSource stylesource = new StreamSource(stylesheet);
Transformer transformer = TransformerFactory.newInstance().newTransformer(stylesource);

StringWriter stringWriter = new StringWriter();
transformer.transform(new DOMSource(document), new StreamResult(stringWriter));

return stringWriter.toString();

The above code works fine if I just use a xml file. How can I achieve what I described above? Thanks in advance!

Update: Got it to work by turning the Excel sheet into a xml file (through Java code) and then following Martin Honnen's answer below.


Assuming you have the XML data exported as an XML document you can pull in the the second document with e.g.

<xsl:param name="sheet-uri" select="'sheet.xml'"/>
<xsl:param name="sheet-doc" select="document($sheet-uri)"/>

and then

                <xsl:for-each select="Sequence/Number">
                        <Number><xsl:value-of select="."/></Number>
                           <xsl:variable name="referenced-code" select="$sheet-doc//Sequence[Number = current()]/Code"/>
                             <xsl:when test="$referenced-code">
                                <xsl:value-of select="$referenced-code"/>