Help needed with Transpose XML and XSLT problem

[infiniti]

Hi,
I am coming across problems in trying to EFFICIENTLY merge to XML
files into one which involves transposing the rows into columns so that
I can either generate a single flat xml file or store it in a flat
table in the database.

The 2 XML files which I have are:

<xml>
<keyword>
<name>abc</name>
<count>143</count>
</keyword>
<keyword>
<name>def</name>
<count>757</count>
</keyword>
<keyword>
<name>ghi</name>
<count>221</count>
</keyword>
</xml>
<xml>
<response searchTerm="abc">
<listing rank="1" bid="0.5"/>
<listing rank="2" bid="6.5"/>
<listing rank="3" bid="4.5"/>
</response>
<response searchTerm="def">
<listing rank="1" bid="4.5"/>
<listing rank="2" bid="34.5"/>
<listing rank="3" bid="1.5"/>
</response>
<response searchTerm="ghi">
<listing rank="1" bid="33.5"/>
<listing rank="2" bid="1.5"/>
<listing rank="3" bid="6.5"/>
</response>
</xml>

I want to merge the 2 xml files into one XML file and later want to
store it into a flat database table.

The structure of the output xml file is:

<xml>
<row keyword="abc" count="143" bid1="0.5" bid2="6.5"
bid3="4.5"/>
<row keyword="def" count="757" bid1="4.5" bid2="34.5"
bid3="1.5"/>
<row keyword="ghi" count="221" bid1="33.5" bid2="1.5"
bid3="6.5"/>
</xml>

Similariy the database table columns will be:
autoid, keyword, count, bid1, bid2, bid3

I am able to achieve this join by parsing the xml file, but I want to
do it as efficiently as possible (I guess using XSLT) as I have to do
this for 10,000+ thousand keywords.

Any help is greatly appreciated.

Thanks,

Amber

[monique]

Hi Amber,
I merged the files in this source

<?xml version="1.0"?>
<xml>
<keyword>
<name>abc</name>
<count>143</count>
</keyword>
<keyword>
<name>def</name>
<count>757</count>
</keyword>
<keyword>
<name>ghi</name>
<count>221</count>
</keyword>
<response searchTerm="abc">
<listing rank="1" bid="0.5"/>
<listing rank="2" bid="6.5"/>
<listing rank="3" bid="4.5"/>
</response>
<response searchTerm="def">
<listing rank="1" bid="4.5"/>
<listing rank="2" bid="34.5"/>
<listing rank="3" bid="1.5"/>
</response>
<response searchTerm="ghi">
<listing rank="1" bid="33.5"/>
<listing rank="2" bid="1.5"/>
<listing rank="3" bid="6.5"/>
</response>
</xml>

after that, I transformed it with the stylesheet:

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

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

<xsl:template match="xml">
<xml>
<xsl:for-each select="./keyword">
<row>
<xsl:attribute name="keyword">
<xsl:value-of select="./name"/>
</xsl:attribute>
<xsl:attribute name="count">
<xsl:value-of select="./count"/>
</xsl:attribute>
<xsl:attribute name="bid1">
<xsl:value-of
select="//response[@searchTerm=current()/name]/listing[@rank='1']/@bid"/>
</xsl:attribute>
<xsl:attribute name="bid2">
<xsl:value-of
select="//response[@searchTerm=current()/name]/listing[@rank='2']/@bid"/>
</xsl:attribute>
<xsl:attribute name="bid3">
<xsl:value-of
select="//response[@searchTerm=current()/name]/listing[@rank='3']/@bid"/>
</xsl:attribute>
</row>
</xsl:for-each>
</xml>
</xsl:template>
</xsl:stylesheet>

it produces the output

<?xml version="1.0"?>
<xml>
<row keyword="abc" count="143" bid1="0.5" bid2="6.5" bid3="4.5"/>
<row keyword="def" count="757" bid1="4.5" bid2="34.5" bid3="1.5"/>
<row keyword="ghi" count="221" bid1="33.5" bid2="1.5" bid3="6.5"/>
</xml>

I tested it into Stylus Studio 2006.

I don't know how to merge two files with XSLT efficiently.
I tried this solution (to me it seems to be a silly way to merge files)

first i created two xml files:

doc1.xml contains the keywords
doc2.xml contains the response

doc1:

<?xml version="1.0"?>
<xml>
<keyword>
<name>abc</name>
<count>143</count>
</keyword>
<keyword>
<name>def</name>
<count>757</count>
</keyword>
<keyword>
<name>ghi</name>
<count>221</count>
</keyword>
</xml>

doc2:

<?xml version="1.0"?>
<xml>
<response searchTerm="abc">
<listing rank="1" bid="0.5"/>
<listing rank="2" bid="6.5"/>
<listing rank="3" bid="4.5"/>
</response>
<response searchTerm="def">
<listing rank="1" bid="4.5"/>
<listing rank="2" bid="34.5"/>
<listing rank="3" bid="1.5"/>
</response>
<response searchTerm="ghi">
<listing rank="1" bid="33.5"/>
<listing rank="2" bid="1.5"/>
<listing rank="3" bid="6.5"/>
</response>
</xml>

now the main xml file:

<?xml version="1.0"?>
<docs>
<file href="doc1.xml"/>
<file href="doc2.xml"/>
</docs>

on this file must be applyed the stylesheet

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

<xsl:variable name="keyword-file" select="document(//file[1]/@href)"/>
<xsl:variable name="response-file"
select="document(//file[2]/@href)"/>

<xsl:template match="/">
<xml>
<xsl:apply-templates select="$keyword-file/xml" mode="1"/>
<xsl:apply-templates select="$response-file/xml" mode="2"/>
</xml>
</xsl:template>

<xsl:template match="xml" mode="1">
<xsl:for-each select="keyword">
<keyword>
<name>
<xsl:value-of select="./name"/>
</name>
<count>
<xsl:value-of select="./count"/>
</count>
</keyword>
</xsl:for-each>
</xsl:template>

<xsl:template match="xml" mode="2">
<xsl:for-each select="response">
<response>
<xsl:attribute name="searchTerm">
<xsl:value-of select="./@searchTerm"/>
</xsl:attribute>
<xsl:for-each select="listing">
<listing>
<xsl:attribute name="rank">
<xsl:value-of select="./@rank"/>
</xsl:attribute>
<xsl:attribute name="bid">
<xsl:value-of select="./@bid"/>
</xsl:attribute>
</listing>
</xsl:for-each>
</response>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

It works with Stylus Studio 2006 built-in parser.
I hope it can be useful.

Please, can anybody tell me a better way to merge files?
Thanks in advance,

Monique

[infiniti]

I just tried it Monique and it works as expected. Thanks :).
I have one more question though, is there a way to generalize the XSLT
to generate the output xml for a variable nummber of bids. e.g. can we
have the xslt to work for 1-10 listings instead of the 1-3 listings it
works for now. I see that you have hardcoded the bid/rank values there.
Its not a big problem though, I can do without it.
Also, can you point me to some good tutorials for learning xslt.

Thanks,

Amber

[monique]

Hi Amber,

These are my favourite tutorials:

http://www.zvon.org/xxl/XSLTutorial/Output/index.html
http://www.w3schools.com/default.asp

I'm trying to remove the hardcoding, any help from the list is
appreciated :-)

Bye

Monique

[monique]

Hi,

here it is:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:apply-templates select="xml" />
</xsl:template>
<xsl:variable name="basename" select='bid' />
<xsl:template match="xml">
<xml>
<xsl:for-each select="./keyword">
<row>
<xsl:attribute name="keyword">
<xsl:value-of select="./name" />
</xsl:attribute>
<xsl:attribute name="count">
<xsl:value-of select="./count" />
</xsl:attribute>
<xsl:variable name="node"
select="//response[@searchTerm=current()/name]" />
<xsl:for-each select="$node/listing">
<xsl:variable name="rank" select="@rank" />
<xsl:attribute name="bid{$rank}">
<xsl:value-of select="@bid" />
</xsl:attribute>
</xsl:for-each>
</row>
</xsl:for-each>
</xml>
</xsl:template>
</xsl:stylesheet>

Bye

Monique