Reading from two DB tables that are related - Yes, it IS an XML thread

[MehmetAli]

Hello Everyone,

Here is my problem. I have an XML file that has the following structure (it looks a bit long, but bear with me, please):

<Root>
	<Manufacturer>
		<Results>
			<Row>
				<ProductId>1</ProductId>
				<ManufacturerID>ManID1</ManufacturerID>
			</Row>
			<Row>
				<ProductId>2</ProductId>
				<ManufacturerID>ManID1</ManufacturerID>
			</Row>
			<Row>
				<ProductId>3</ProductId>
				<ManufacturerID>ManID2</ManufacturerID>
			</Row>
			<Row>
				<ProductId>3</ProductId>
				<ManufacturerID>ManID3</ManufacturerID>
			</Row>
			<Row>
				<ProductId>4</ProductId>
				<ManufacturerID/>
			</Row>
		</Results>
	</Manufacturer>
	<Material>
		<Results>
			<Row>
				<ProductId>1</ProductId>
				<Attribute1>adf1</Attribute1>
				<Attribute2>adf2</Attribute2>
				<Attribute3>adf3</Attribute3>
			</Row>
			<Row>
				<ProductId>2</ProductId>
				<Attribute1>adf4</Attribute1>
				<Attribute2>adf5</Attribute2>
				<Attribute3>adf6</Attribute3>
			</Row>
			<Row>
				<ProductId>3</ProductId>
				<Attribute1>adf7</Attribute1>
				<Attribute2>adf8</Attribute2>
				<Attribute3>adf9</Attribute3>
			</Row>
			<Row>
				<ProductId>4</ProductId>
				<Attribute1>qwe1</Attribute1>
				<Attribute2>qwe2</Attribute2>
				<Attribute3>qwe3</Attribute3>
			</Row>
		</Results>
	</Material>
</Root>

The result file should look like this:

<Root>
	<MaterialwithManID>
		<Results>
			<Row>
				<ProductId>1</ProductId>
				<Attribute1>adf1</Attribute1>
				<Attribute2>adf2</Attribute2>
				<Attribute3>adf3</Attribute3>
				<ManufacturerID>ManID1</ManufacturerID>

			</Row>
			<Row>
				<ProductId>2</ProductId>
				<Attribute1>adf4</Attribute1>
				<Attribute2>adf5</Attribute2>
				<Attribute3>adf6</Attribute3>
				<ManufacturerID>ManID1</ManufacturerID>

			</Row>
			<Row>
				<ProductId>3</ProductId>
				<Attribute1>adf7</Attribute1>
				<Attribute2>adf8</Attribute2>
				<Attribute3>adf9</Attribute3>
				<ManufacturerID>ManID2</ManufacturerID>
			</Row>
			<Row>
				<ProductId>3</ProductId>
				<Attribute1>qwe1</Attribute1>
				<Attribute2>qwe2</Attribute2>
				<Attribute3>qwe3</Attribute3>
				<ManufacturerID>ManID3</ManufacturerID>

			</Row>
			<Row>
				<ProductId>4</ProductId>
				<Attribute1>qwe4</Attribute1>
				<Attribute2>qwe5</Attribute2>
				<Attribute3>qwe6</Attribute3>
				<ManufacturerID/>
			</Row>
		</Results>
	</MaterialwithManID>
</Root>

The trick is to match the manufacturer IDs to the relevant products, based on the product ID. One product can have more than one manufacturer, so I have to read each manufacturer entry and check if the product ID matches with that of the product entry, repeating the entry as much as necessary for each manufacturer that delivers the product. The question is, how?

I appreciate sample code snippets if you can provide me with some.
Thanks in advance.
Cheers
Memo

 

[jel]

You could use an xsl-transformation, for example:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
   <xsl:output method="xml" version="1.0" indent="yes" />

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

   <xsl:template match="Manufacturer" />

   <xsl:template match="Row">
      <xsl:variable name="MaterialRow" select="." />
      <xsl:variable name="ID" select="ProductId" />
      <xsl:variable name="ManufacturerRow" select="//Manufacturer/Results/Row[ProductId=$ID]" />
      <xsl:for-each select="$ManufacturerRow">
         <xsl:element name="Row">
            <xsl:apply-templates select="$MaterialRow/*" />
            <xsl:copy-of select="ManufacturerID" />
         </xsl:element>
      </xsl:for-each>
   </xsl:template>

   <xsl:template match="*">
      <xsl:element name="{name()}">
         <xsl:apply-templates />
      </xsl:element>
   </xsl:template>

</xsl:stylesheet>