Transforming Excel "XML"

F

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">
<xsl:eek:utput 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!
 
J

Joris Gillis

Tempore 05:58:50 said:
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/>
Click to expand...
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">
<xsl:eek:utput 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.
Click to expand...
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,
 
F

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


Joris said:
Tempore 05:58:50, die Thursday 10 February 2005 AD, hinc in foro
Click to expand...
{comp.text.xml} scripsit Ferd Biffle said:
I would use a nested decision structure packed up in one template
Click to expand...
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">
<xsl:eek:utput method="xml" indent="yes"/>

<xsl:key name="row" match="Row"
Click to expand...
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
Click to expand...
Released said:
</SubPart1><SubPart1>
Click to expand...
</SubPart2></PartNum>
</SubPart1><SubPart1>
<PartNum>2211845-001<PartType>PART<LifeCyclePhase>Engineering
Click to expand...
Released said:
<PartNum>2211845<PartType>[Drawing<LifeCyclePhase>Engineering
Click to expand...
Released said:
</SubPart2><SubPart2>
Click to expand...
</SubPart3></PartNum>
</SubPart2></PartNum>
</SubPart1></PartNum>
</SubPart0>
</Root>




I can't seem to get the "if test following-sibling" syntax to work
correctly.
Click to expand...
The context node is a 'Row' element, when you prefer this test, so
Click to expand...
you'll never get a following sibling named 'Level'.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Passing a javascript variable to an external xsl/xml file 1
xsl not transforming correctly 2
xsltproc question - wondering what I did wrong? 4
XSLT embedding (positional grouping key?) problem. newbie 2
Problem in xml transformation using xslt 0
XSLT2.0 temporary nodeset as parameter 2
Stumped Transforming DSML Data With XSLT` 2
XSLT to create filtered subset 6

Members online

No members online now.
Total: 28 (members: 0, guests: 28)
Robots: 444

Forum statistics

Threads
473,770
Messages
2,569,583
Members
45,074
Latest member
StanleyFra

Latest Threads

Top