Price an XML file

[undrmoons]

I have an XML file containing product information.
I have another XML file containing pricing for specific products.
Both XML files contain SKU information as a point of reference.

I'm trying to figure out how to go about creating this "look up" and replace
the "00.00" price amount according to the 2nd XML file due to the SKU's
being out of sequence from one XML file to the next.

--------------------------------------------------
This is the product page:
<Root>
<Page>
<Story>
<Table>
<row>
<Cell><SKU>456291</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>456225</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>456209</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
</Table>
<Table>
<row>
<Cell><SKU>574598</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>430937</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>481443</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>570194</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>484697</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>484555</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>484542</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>574590</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>484601</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>484572</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
<row>
<Cell><SKU>571029</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
</Table>
</Story>
</Page>
</Root>

---------------------------------------------------
This is the XML Price Sheet:

<?xml version="1.0" encoding="UTF-8"?>
<root>

<row>
<SKU>456209</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>574598</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>484572</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>571029</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>481443</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>456291</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>456225</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>570194</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>484697</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>484601</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>430937</SKU>
<Price>159.99</Price>
</row>
<row>
<SKU>484555</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>484542</SKU>
<Price>259.99</Price>
</row>
<row>
<SKU>574590</SKU>
<Price>259.99</Price>
</row>
</root>


-----------------------------------

I'm a bit stuck on how to even go about this. But it seems simple enough...
just out of reach for my knowledge though.


Thanks in advance!

[Radu]

Hi,

Try the following stylesheet:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

    <xsl:output indent="yes"/>

    <xsl:template match="Root">

        <root>

            <xsl:for-each select="Page">

                <Page>

                    <xsl:for-each select="Story">

                        <Story>

                            <xsl:for-each select="Table">

                                <Table>

                                    <xsl:for-each select="row">

                                        <row>

                                            <xsl:variable name="var" select="Cell/SKU/text()"/>

                                            <Cell>

                                                <SKU>

                                                  <xsl:value-of select="$var"/>

                                                </SKU>

                                            </Cell>

                                            <Cell>

                                                <Price>

                                                    <xsl:variable name="correspPrice" select="

                                                        document('s2.xml')/root/row/Price/text()[../../SKU/text()=$var]"/>

                                                    <xsl:value-of select="$correspPrice"/>

                                                </Price>

                                            </Cell>

                                        </row>

                                    </xsl:for-each>

                                </Table>

                            </xsl:for-each>

                        </Story>

                    </xsl:for-each>

                </Page>

            </xsl:for-each>

        </root>

    </xsl:template>

</xsl:stylesheet>

The stylesheet will be executed against the first xml document (with the prices set to 0.00) but the second document (I named it "s2.xml") will have to be in the same directory with the stylesheet. As you can see, I output the first document virtually unchanged but, instead of outputing its price value I output the price value from the second document where the SKU value matches.

Regards, Radu.

[undrmoons]

Thanks Radu!

That's a big help, and works great with the example I gave you.

Here's a situation I encountered though — on the rare case I'll have several SKU's but only one Price. Which breaks up the XML a bit.

It sometimes looks like this:

------------------------------------------
<Table>
<row>
<Cell><SKU>456291</SKU></Cell>
<Cell><SKU>456225</SKU></Cell>
<Cell><SKU>456209</SKU></Cell>
<Cell><Price>00.00</Price></Cell>
</row>
</Table>
------------------------------------------

In this specific instance, my only concern is the first SKU by which the price
is listed. So I have to isolate that first SKU somehow and ignore the others
while still switching the "00.00" price with the real price from the price file.

Should I try to count for first SKU? I get lost on when and where to do that.

thanks!

[jkmyoung]

If it's always in the first cell in the row you could change to:
<xsl:variable name="var" select="Cell/SKU[1]"/>
If not always in the first cell in the row:
<xsl:variable name="var" select="(Cell/SKU)[1]"/>


Also in the corresponding price var I recommend using
<xsl:variable name="correspPrice" select="document('s2.xml')/root/row[SKU=$var]/Price"/>

which should make it considerably faster, as you're checking if the row matches the criteria before getting the price value over and over.

[jkmyoung]

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

	<xsl:output indent="yes"/>

	<xsl:template match="Root|Page|Story|Table">

		<xsl:copy>

			<xsl:apply-templates/>

		</xsl:copy>

	</xsl:template>

	<xsl:template match="row">

		<row>

			<xsl:variable name="var" select="(Cell/SKU)[1]"/>

			<xsl:for-each select="Cell/SKU">

				<Cell>

					<SKU>

						<xsl:value-of select="."/>

					</SKU>

				</Cell>

			</xsl:for-each>

			<Cell>

				<Price>

					<xsl:value-of select="document('s2.xml')/root/row[SKU = $var]/Price"/>

				</Price>

			</Cell>

		</row>

	</xsl:template>

</xsl:stylesheet>

[jkmyoung]

Actually even more compact: This one represents more of what you're trying to do, copying everything, and editing only the Price. (wish they had edit post capability here)

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

	<xsl:output indent="yes"/>

	<xsl:template match="Price">

		<xsl:variable name="var" select="(../../Cell/SKU)[1]"/>

		<Price>

			<xsl:value-of select="document('s2.xml')/root/row[SKU = $var]/Price"/>

		</Price>

	</xsl:template>

	<xsl:template match="*">

		<xsl:copy>

			<xsl:apply-templates/>

		</xsl:copy>

	</xsl:template>

</xsl:stylesheet>