Using XSLT to take data from two XML files and merge into one?

Question

I'm trying to merge two tables using XSLT 1.0.

I have a representation of two DB tables in XML. The first table is a set of key-value pairs:

Table1.xml

<table>
  <row>
    <column name="key">key1</column>
    <column name="value">val1</column>
  </row>
  <row>
    <column name="key">key2</column>
    <column name="value">val2</column>
  </row>
</table>

The second table has rows of data:

Table2.xml

<table>
  <row>
    <column name="A">a1</column>
    <column name="B">b1</column>
    <column name="C">c1</column>
  </row>
  <row>
    <column name="A">a2</column>
    <column name="B">b2</column>
    <column name="C">c2</column>
  </row>
</table>

I am trying to take one of the key-value pairs and add insert it as a new column in every row so that it ends up with this:

Output.xml

<table>
  <row>
    <column name="A">a1</column>
    <column name="B">b1</column>
    <column name="C">c1</column>
    <column name="key1">val1</column>
  </row>
  <row>
    <column name="A">a2</column>
    <column name="B">b2</column>
    <column name="C">c2</column>
    <column name="key1">val1</column>
  </row>
</table>

"I am trying to take one of the key-value pairs" What determines which one? — michael.hor257k
– michael.hor257k, Commented Nov 17, 2016 at 21:43
The value of the key is what I'm predicating the decision upon. If column = key1 then select val1. — Chris Roberts
– Chris Roberts, Commented Nov 18, 2016 at 14:01
@LarsH As of now I haven't tried too many solutions. I have spent the better part of the day trying to learn XSLT by going through various tutorials to try and figure this out.. — Chris Roberts
– Chris Roberts, Commented Nov 18, 2016 at 14:17
"The value of the key is what I'm predicating the decision upon." I am afraid that doesn't say much. There are two rows, and each row has a different key/value pair. Why did you pick the first row and ignored the second one? — michael.hor257k
– michael.hor257k, Commented Nov 18, 2016 at 14:42
"If column = key1 then select val1." If what column = key1? — LarsH
– LarsH, Commented Nov 18, 2016 at 14:49

Michael Kay · Accepted Answer · 2016-11-18 14:37:35Z

1

Here are the two main ingredients of the solution:

<xsl:variable name="kvp">
  <xsl:variable name="row" select="doc('table1.xml')/table/row[1]"/>
  <column name="{$row/column[1]}">
    <xsl:value-of select="$row/column[2]"/>
  </column>
</xsl:variable>

<xsl:template match="row">
  <row>
    <xsl:copy-of select="*"/>
    <xsl:copy-of select="$kvp"/>
  </row>
</xsl:template>

edited Nov 18, 2016 at 14:37

answered Nov 17, 2016 at 23:13

Michael Kay

165k11 gold badges97 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

michael.hor257k Over a year ago

This is XSLT 2.0 syntax. OP uses XSLT 1.0.

Michael Kay Over a year ago

Now that XSLT 2.0 is ten years old, I'm having great trouble remembering how to write XSLT 1.0 code, but I've made an attempt.

michael.hor257k · Accepted Answer · 2016-11-18 15:43:03Z

I want to select the row where the key is "key1" and create a new column from it in the output table.

Assuming you are processing the Table2.xml file, you can do:

XSLT 1.0

<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:strip-space elements="*"/>

<xsl:variable name="val" select="document('Table1.xml')/table/row[column[1]='key1']/column[2]" />

<!-- identity transform -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="row">
    <xsl:copy>
        <xsl:apply-templates/>
        <column name="key1">
            <xsl:value-of select="$val" />
        </column>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

Collectives™ on Stack Overflow

Using XSLT to take data from two XML files and merge into one?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related