How do i change XML to XML using XSLT

Question

Hi im trying to change my XML document using XSLT. I have tried to follow some of the other threads about the topic on Stack Overflow, but i can't get it working. My XML file is a database export and it's the only way i can export all my data at once, with the software i'm using. But the layout of the exported data won't work in the program i am extracting the file too, so i need to change the layout of the XML before i import it to the new program.

This is my original XML.

<?xml version="1.0" encoding="UTF-8"?>
<Log>
  <DataSource>test</DataSource>
  <SQLStatement>SELECT * FROM test_elementer WHERE country='FR';</SQLStatement>
  <SQLStatementResult>Success</SQLStatementResult>
  <SQLStatementValue>
    <Row>
      <Column Name="id" DataType="INTEGER">1</Column>
      <Column Name="country" DataType="STRING">FR</Column>
      <Column Name="city" DataType="STRING">Lyon</Column>
      <Column Name="windows_1000x1000" DataType="INTEGER">1</Column>
      <Column Name="windows_1000x750" DataType="INTEGER">2</Column>
      <Column Name="windows_1250x1250" DataType="INTEGER">3</Column>
      <Column Name="street_700x1000" DataType="INTEGER">4</Column>
    </Row>
    <Row>
      <Column Name="id" DataType="INTEGER">2</Column>
      <Column Name="country" DataType="STRING">FR</Column>
      <Column Name="city" DataType="STRING">Herblay</Column>
      <Column Name="windows_1000x1000" DataType="INTEGER">4</Column>
      <Column Name="windows_1000x750" DataType="INTEGER">2</Column>
      <Column Name="windows_1250x1250" DataType="INTEGER">3</Column>
      <Column Name="street_700x1000" DataType="INTEGER">1</Column>
    </Row>
    <Row>
      <Column Name="id" DataType="INTEGER">3</Column>
      <Column Name="country" DataType="STRING">FR</Column>
      <Column Name="city" DataType="STRING">Nantes</Column>
      <Column Name="windows_1000x1000" DataType="INTEGER">5</Column>
      <Column Name="windows_1000x750" DataType="INTEGER">3</Column>
      <Column Name="windows_1250x1250" DataType="INTEGER">1</Column>
      <Column Name="street_700x1000" DataType="INTEGER">4</Column>
    </Row>
  </SQLStatementValue>
</Log>

I need to convert this XML to the following:

<data>
  <store>
    <id>1</id>
    <country>FR</country>
    <city>Lyon</city>
    <windows_1000x1000>1</windows_1000x1000>
    <windows_1000x750>2</windows_1000x750>
    <windows_1250x1250>3</windows_1250x1250>
    <windows_700x1000>4</windows_700x1000>
  </store>
  <store>
    <id>2</id>
    <country>FR</country>
    <city>Herblay</city>
    <windows_1000x1000>4</windows_1000x1000>
    <windows_1000x750>2</windows_1000x750>
    <windows_1250x1250>3</windows_1250x1250>
    <windows_700x1000>1</windows_700x1000>
  </store>
  <store>
    <id>3</id>
    <country>FR</country>
    <city>Nantes</city>
    <windows_1000x1000>5</windows_1000x1000>
    <windows_1000x750>3</windows_1000x750>
    <windows_1250x1250>1</windows_1250x1250>
    <windows_700x1000>4</windows_700x1000>
  </store>
</data>

This is the XSL code i tried:

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
    <xsl:output indent="yes"/>

    <xsl:template match="/">
        <data>
            <xsl:apply-templates select="Log/SQLStatementValue/Row" /> 
        </data>
    </xsl:template>

    <xsl:template match="Row">
        <store>
            <xsl:apply-templates select="Column[@name = 'id']"/>
            <xsl:apply-templates select="Column[@name = 'country']"/>
            <xsl:apply-templates select="Column[@name = 'city']"/>
            <xsl:apply-templates select="Column[@name = 'windows_1000x1000']"/>
            <xsl:apply-templates select="Column[@name = 'windows_1000x750']"/>
            <xsl:apply-templates select="Column[@name = 'windows_1250x1250']"/>
            <xsl:apply-templates select="Column[@name = 'street_700x1000']"/>
        </store>
    </xsl:template>

    <xsl:template match="Column[@name = 'id']">
        <id>
        <xsl:value-of select="."/>
        </id>
    </xsl:template>

    <xsl:template match="Column[@name = 'country']">
        <country>
        <xsl:value-of select="."/>
        </country>
    </xsl:template>

    <xsl:template match="Column[@name = 'city']">
        <city>
        <xsl:value-of select="."/>
        </city>
    </xsl:template>

    <xsl:template match="Column[@name = 'windows_1000x1000']">
        <windows_1000x1000>
        <xsl:value-of select="."/>
        </windows_1000x1000>
    </xsl:template>
    
    <xsl:template match="Column[@name = 'windows_1000x750']">
        <windows_1000x750>
        <xsl:value-of select="."/>
        </windows_1000x750>
    </xsl:template>

    <xsl:template match="Column[@name = 'windows_1250x1250']">
        <windows_1250x1250>
        <xsl:value-of select="."/>
        </windows_1250x1250>
    </xsl:template>

    <xsl:template match="Column[@name = 'street_700x1000']">
        <street_700x1000>
        <xsl:value-of select="."/>
        </street_700x1000>
    </xsl:template>

</xsl:transform>

Perhaps changing Column[@name to Column[@Name in all your expressions improves things as otherwise you predicate doesn't match the spelling and case of the XML attribute. But you haven't really explained how/where your attempt fails, i.e. which error or wrong output you get. — Martin Honnen
– Martin Honnen, Commented Dec 17, 2020 at 13:05
It worked, Thanks a lot! The problem was that i didn't get any data out, the store tag was just empty. — Mich237i
– Mich237i, Commented Dec 17, 2020 at 13:27

Michael Kay · Accepted Answer · 2020-12-17 13:24:37Z

1

The attribute is called Name, not name.

Note also you can replace the individual template rules with a generic one:

<xsl:template match="Column">
   <xsl:element name="{@Name}">
      <xsl:value-of select="."/>
   </xsl:element>
</xsl:template>

answered Dec 17, 2020 at 13:24

Michael Kay

165k11 gold badges97 silver badges173 bronze badges

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

Collectives™ on Stack Overflow

How do i change XML to XML using XSLT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related