Transforming Excel "XML"

[Ferd Biffle]

Hello!

I have an Excel XML feed from an Enterprise PDM system that is
basically a flat text file with XML tags:

<?xml version="1.0" encoding="UTF-8"?>
<Import>
<Row>
<ID>1</ID>
<Level>0</Level>
<PartNumber>2211845-500</PartNumber>
<ItemNum>1</ItemNum>
</Row>
<Row>
<ID>2</ID>
<Level>1</Level>
<PartNumber>2211845</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>1</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Engineering Released</ItemLifecyclePhase>
<ItemType>[Drawing</ItemType>
</Row>
<Row>
<ID>3</ID>
<Level>1</Level>
<PartNumber>2213090</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>A</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Production Released</ItemLifecyclePhase>
<ItemType>[Specification</ItemType>
</Row>
<Row>
<ID>4</ID>
<Level>2</Level>
<PartNumber>MIL-C-5541</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>E</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Production Released</ItemLifecyclePhase>
<ItemType>[Standard</ItemType>
</Row>
<Row>
<ID>5</ID>
<Level>1</Level>
<PartNumber>2211845-001</PartNumber>
<ItemNum>1</ItemNum>
<Qty>0.001</Qty>
<UM>EA</UM>
<Rev>1</Rev>
<MakeBuy>M</MakeBuy>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Engineering Released</ItemLifecyclePhase>
<ItemType>PART</ItemType>
</Row>
<Row>
<ID>6</ID>
<Level>2</Level>
<PartNumber>2211845</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>1</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Engineering Released</ItemLifecyclePhase>
<ItemType>[Drawing</ItemType>
</Row>
<Row>
<ID>7</ID>
<Level>2</Level>
<PartNumber>MIL-C-7438</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>G</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Production Released</ItemLifecyclePhase>
<ItemType>[Standard</ItemType>
</Row>
<Row>
<ID>8</ID>
<Level>3</Level>
<PartNumber>MIL-C-7439</PartNumber>
<ItemNum>0</ItemNum>
<Qty>0</Qty>
<Rev>G</Rev>
<SubsOK>Yes</SubsOK>
<ItemLifecyclePhase>Production Released</ItemLifecyclePhase>
<ItemType>[Standard</ItemType>
</Row>
</Import>

The items are read in order and related through the LEVEL tag.
Therefore the desired structure of a transformed XML file would be:

Level0 - Root
Level1
Level1
|--------|
| Level2
|
Level1
|--------|------------|
Level2 Level2
|--------|
Level3

I have tried to access the "subLevels" in the following manner,
but haven't had success:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="xml"/>

<xsl:template match="/">
<Root>
<xsl:apply-templates/>
</Root>
</xsl:template>
<xsl:template match="Import">
<Assembly>
<AssemblyName>
<xsl:value-of select="Row/ReferenceNo"/>
</AssemblyName>
</Assembly>
<xsl:apply-templates select="Row"/>
</xsl:template>
<xsl:template match="Row">
<xsl:if test="Level = 1">
<xsl:for-each select=".">
<PartNum>
<xsl:value-of select="PartNumber"/>
<PartType>
<xsl:value-of select="ItemType"/>
<LifeCyclePhase>
<xsl:value-of select="ItemLifecyclePhase"/>
</LifeCyclePhase>
</PartType>
<xsl:if test="following-sibling::Level = 2">
<SubPart>
<PartNum>
<xsl:value-of select="PartNumber"/>
<PartType>
<xsl:value-of select="ItemType"/>
<LifeCyclePhase>
<xsl:value-of select="ItemLifecyclePhase"/>
</LifeCyclePhase>
</PartType>
<xsl:if test="following-sibling::Level = 3">
<SubPart2>
<PartNum>
<xsl:value-of select="PartNumber"/>
<PartType>
<xsl:value-of select="ItemType"/>
<LifeCyclePhase>
<xsl:value-of select="ItemLifecyclePhase"/>
</LifeCyclePhase>
</PartType>
</PartNum>
</SubPart2>
</xsl:if>
</PartNum>
</SubPart>
</xsl:if>
</PartNum>
</xsl:for-each>
</xsl:if>
</xsl:template>
</xsl:stylesheet>

I can't seem to get the "if test following-sibling" syntax to work
correctly. I've looked all over the web for examples but no dice.

Does anyone have any pointers they could offer, or maybe a different
approach that might work for this example?

Thanks in advance for any help provided!

 

[Joris Gillis]

The items are read in order and related through the LEVEL tag.
Therefore the desired structure of a transformed XML file would be:

Level0 - Root
Level1
Level1
|--------|
| Level2
|
Level1
|--------|------------|
Level2 Level2
|--------|
Level3

I have tried to access the "subLevels" in the following manner,
but haven't had success:

<code/>

I would use a nested decision structure packed up in one template like this, especially if the nesting level of your input XML may increase.

Maybe you could try a recursive solution like this:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="xml" indent="yes"/>

<xsl:key name="row" match="Row" use="generate-id(preceding-sibling::Row[current()/Level - Level=1][1])"/>

<xsl:template match="/">
<Root>
<xsl:apply-templates/>
</Root>
</xsl:template>

<xsl:template match="Import">
<Assembly>
<AssemblyName>
<xsl:value-of select="Row/ReferenceNo"/>
</AssemblyName>
</Assembly>
<xsl:apply-templates select="Row[number(Level)=0]"/>
</xsl:template>

<xsl:template match="Row">
<xsl:element name="SubPart{Level}">
<PartNum>
<xsl:value-of select="PartNumber"/>
<PartType>
<xsl:value-of select="ItemType"/>
<LifeCyclePhase>
<xsl:value-of select="ItemLifecyclePhase"/>
</LifeCyclePhase>
</PartType>
<xsl:apply-templates select="key('row',generate-id())"/>
</PartNum>
</xsl:element>
</xsl:template>

</xsl:stylesheet>


output:
<Root>
<Assembly>
<AssemblyName></AssemblyName>
</Assembly>
<SubPart0>
<PartNum>2211845-500<PartType><LifeCyclePhase></LifeCyclePhase></PartType><SubPart1>
<PartNum>2211845<PartType>[Drawing<LifeCyclePhase>Engineering Released</LifeCyclePhase></PartType></PartNum>
</SubPart1><SubPart1>
<PartNum>2213090<PartType>[Specification<LifeCyclePhase>Production Released</LifeCyclePhase></PartType><SubPart2>
<PartNum>MIL-C-5541<PartType>[Standard<LifeCyclePhase>Production Released</LifeCyclePhase></PartType></PartNum>
</SubPart2></PartNum>
</SubPart1><SubPart1>
<PartNum>2211845-001<PartType>PART<LifeCyclePhase>Engineering Released</LifeCyclePhase></PartType><SubPart2>
<PartNum>2211845<PartType>[Drawing<LifeCyclePhase>Engineering Released</LifeCyclePhase></PartType></PartNum>
</SubPart2><SubPart2>
<PartNum>MIL-C-7438<PartType>[Standard<LifeCyclePhase>Production Released</LifeCyclePhase></PartType><SubPart3>
<PartNum>MIL-C-7439<PartType>[Standard<LifeCyclePhase>Production Released</LifeCyclePhase></PartType></PartNum>
</SubPart3></PartNum>
</SubPart2></PartNum>
</SubPart1></PartNum>
</SubPart0>
</Root>

I can't seem to get the "if test following-sibling" syntax to work
correctly.

The context node is a 'Row' element, when you prefer this test, so you'll never get a following sibling named 'Level'.
Probably you meant:
<xsl:if test="Level = '2'" />
or
<xsl:if test="number(Level) = 2" />


regards,

 

[Ferd Biffle]

Hey Joris!

That did the trick - I banged my head on this one all yesterday
afternoon. I'm jumping back into XML\XSL
after taking an MOC class a year and a half ago. Amazing what you
forget

Anyhow - thanks for the lesson!

Ferd

Tempore 05:58:50, die Thursday 10 February 2005 AD, hinc in foro

I would use a nested decision structure packed up in one template

like this, especially if the nesting level of your input XML may
increase.

Maybe you could try a recursive solution like this:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="xml" indent="yes"/>

<xsl:key name="row" match="Row"

use="generate-id(preceding-sibling::Row[current()/Level -
Level=1][1])"/>

<xsl:template match="/">
<Root>
<xsl:apply-templates/>
</Root>
</xsl:template>

<xsl:template match="Import">
<Assembly>
<AssemblyName>
<xsl:value-of select="Row/ReferenceNo"/>
</AssemblyName>
</Assembly>
<xsl:apply-templates select="Row[number(Level)=0]"/>
</xsl:template>

<xsl:template match="Row">
<xsl:element name="SubPart{Level}">
<PartNum>
<xsl:value-of select="PartNumber"/>
<PartType>
<xsl:value-of select="ItemType"/>
<LifeCyclePhase>
<xsl:value-of select="ItemLifecyclePhase"/>
</LifeCyclePhase>
</PartType>
<xsl:apply-templates select="key('row',generate-id())"/>
</PartNum>
</xsl:element>
</xsl:template>

</xsl:stylesheet>


output:
<Root>
<Assembly>
<AssemblyName></AssemblyName>
</Assembly>
<SubPart0>

<PartNum>2211845<PartType>[Drawing<LifeCyclePhase>Engineering

</SubPart1><SubPart1>

</SubPart2></PartNum>
</SubPart1><SubPart1>
<PartNum>2211845-001<PartType>PART<LifeCyclePhase>Engineering

<PartNum>2211845<PartType>[Drawing<LifeCyclePhase>Engineering

</SubPart2><SubPart2>

</SubPart3></PartNum>
</SubPart2></PartNum>
</SubPart1></PartNum>
</SubPart0>
</Root>




I can't seem to get the "if test following-sibling" syntax to work
correctly.

The context node is a 'Row' element, when you prefer this test, so

you'll never get a following sibling named 'Level'.