Flow Flow - 5 months ago 30
Java Question

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

Is there a way I can make my

xslt
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
xslt
:

<?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">
<BBB>
<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:if>
<Code>0</Code>
<xsl:for-each select="Sequence/Number">
<Result>
<Number><xsl:value-of select="."/></Number>
<Code>0</Code>
</Result>
</xsl:for-each>
</BBB>
</Data>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>


Excel Sheet:

A B

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


The xml:

<Data xmlns="MyProtocol.xsd">
<AAA>
<Id>2</Id>
<Timestamp>2016-31-12</Timestamp>
<Sequence>
<Number>5556667777</Number>
<Number>5559877890</Number>
<Number>5552835291</Number>
</Sequence>
</AAA>
</Data>


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


  • Read the
    <Number>
    from the xml file

  • Check if that number is in the Excel sheet

  • Assign the correct
    <Code>
    number for that number in the
    xslt



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()
.newDocumentBuilder().parse(inputSource);

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.

Answer

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">
                    <Result>
                        <Number><xsl:value-of select="."/></Number>
                        <Code>
                           <xsl:variable name="referenced-code" select="$sheet-doc//Sequence[Number = current()]/Code"/>
                           <xsl:choose>
                             <xsl:when test="$referenced-code">
                                <xsl:value-of select="$referenced-code"/>
                             </xsl:when>
                             <xsl:otherwise>0</xsl:otherwise>
                           </xsl:choose>
                        </Code>
                    </Result>
                </xsl:for-each>
Comments