Filter xml data based on information from another xml file

[chriselectrix]

Hi

I have to XML files and two XSL and HTML documents.

I have a 'master' xml file, but I also have a 'slave'.

I cant edit the xml files as they are automatically exported from a software package.

Table 1 (master) contains e.g.:

Master Job Master Part Qty Child Part Required

0123456 Big Toy 2 Small Toy A False
0123456 Big Toy 2 Small Toy B True
0123456 Big Toy 1 Small Toy C True

Table 2 (child - separate xml) contains e.g.:

Child Job Child Part Order Date

5564789 Small Toy B 01/05/11
6945311 Small Toy C 09/09/11

So in this example, Small Toy B is in both sets of data and Required = True therefore it is displayed in Table 2.

If it was set to false, it wouldn't be required in table 2.

The filter for table 2 needs to be something like ...

Child_Part in Table 2 = Child_Part in Table 1 AND when Required = TRUE

Any suggestions would be appreciated.

[iceandrews]

You can use the document() function to bring the other XML document into the XSLT as a variable.

<xsl:variable name="SlaveDoc" select="document('Your_Doc.xml')" />

Once it's stored a variable you can navigate that node set just like any other. You can write and Xpath that says Get me nodes that "Child_Part in Table 2 = Child_Part in Table 1 AND when Required = TRUE" pretty easily.

[chriselectrix]

Im new to this so Im not too sure what you mean.

This is a sample of my PARENT XML data ...

<?xml version="1.0" encoding="utf-8"?>
<ExportQuery>
  <ELEC01_SalesOrdersWaitingParts>
    <Customer_CustID><![CDATA[V027]]></Customer_CustID>
    <OrderHed_RequestDate><![CDATA[01/02/11]]></OrderHed_RequestDate>
    <OrderHed_OrderDate><![CDATA[24/02/11]]></OrderHed_OrderDate>
    <OrderHed_NeedByDate><![CDATA[07/03/11]]></OrderHed_NeedByDate>
    <OrderHed_OrderNum><![CDATA[19122]]></OrderHed_OrderNum>
    <OrderHed_OpenOrder><![CDATA[Open]]></OrderHed_OpenOrder>
    <OrderDtl_OrderNum><![CDATA[19122]]></OrderDtl_OrderNum>
    <OrderDtl_OrderLine><![CDATA[1]]></OrderDtl_OrderLine>
    <OrderDtl_OrderQty><![CDATA[10.00]]></OrderDtl_OrderQty>
    <OrderDtl_PartNum><![CDATA[S17 150-150]]></OrderDtl_PartNum>
    <Part_PartNum><![CDATA[S17 150-150]]></Part_PartNum>
    <TotalOnHandQty><![CDATA[1.00]]></TotalOnHandQty>
    <TotalDemandQty><![CDATA[35.00]]></TotalDemandQty>
    <OnHandDemandLine><![CDATA[-24.00]]></OnHandDemandLine>
    <JobHead_PartNum><![CDATA[S17 150-150]]></JobHead_PartNum>
    <JobHead_JobNum><![CDATA[018816]]></JobHead_JobNum>
    <JobHead_JobComplete><![CDATA[No]]></JobHead_JobComplete>
    <JobOper_JobNum><![CDATA[018816]]></JobOper_JobNum>
    <JobOper_OpCode><![CDATA[INSP PR6]]></JobOper_OpCode>
    <JobOper_OpDesc><![CDATA[INSP FOLD PR6]]></JobOper_OpDesc>
    <JobOper_OpComplete><![CDATA[No]]></JobOper_OpComplete>
    <JobOper_QtyCompleted><![CDATA[0.00]]></JobOper_QtyCompleted>
    <NotEnoughStock><![CDATA[TRUE]]></NotEnoughStock>
    <JobMtl_JobNum><![CDATA[018816]]></JobMtl_JobNum>
    <JobMtl_PartNum><![CDATA[1.4301 2J PC1 1500 X 1250 X 1.2]]></JobMtl_PartNum>
    <JobMtl_RequiredQty><![CDATA[0.86]]></JobMtl_RequiredQty>
  </ELEC01_SalesOrdersWaitingParts>

This is a sample of my CHILD table's data ...

<?xml version="1.0" encoding="utf-8"?>
<ExportQuery>
  <ELEC01_SubAssemblyWIPStock>
    <Part_PartNum><![CDATA[20mm TUBE 1m 201]]></Part_PartNum>
    <TotalOnHandQty><![CDATA[19.51]]></TotalOnHandQty>
    <TotalNonNettable><![CDATA[0.00]]></TotalNonNettable>
    <TotalDemandQty><![CDATA[0.00]]></TotalDemandQty>
    <Required><![CDATA[FALSE]]></Required>
    <JobHead_PartNum><![CDATA[20mm TUBE 1m 201]]></JobHead_PartNum>
    <JobHead_JobNum><![CDATA[018267]]></JobHead_JobNum>
    <JobHead_JobComplete><![CDATA[No]]></JobHead_JobComplete>
    <JobOper_JobNum><![CDATA[018267]]></JobOper_JobNum>
    <JobOper_OpCode><![CDATA[INSP DRE]]></JobOper_OpCode>
    <JobOper_OpDesc><![CDATA[INSP DRESS]]></JobOper_OpDesc>
    <JobOper_OpComplete><![CDATA[No]]></JobOper_OpComplete>
    <JobOper_QtyCompleted><![CDATA[0.00]]></JobOper_QtyCompleted>
    <JobMtl_JobNum><![CDATA[018267]]></JobMtl_JobNum>
    <JobMtl_PartNum><![CDATA[20mm TUBE 304 BI]]></JobMtl_PartNum>
    <JobMtl_RequiredQty><![CDATA[524.99]]></JobMtl_RequiredQty>
    <Part_ProdCode><![CDATA[]]></Part_ProdCode>
  </ELEC01_SubAssemblyWIPStock>

This is my PARENT XSL which displays all lines where 'NotEnoughStock=TRUE' ...

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" omit-xml-declaration="yes"/>

<xsl:template match="/">
  <html>
  <body bgcolor="white">
Orders to be shipped before TODAY + 7 days
    <table cellpadding="4px">
      <tr>
	<td align="center"><B1>Customer</B1></td>
        <td align="center"><B1>Order No.</B1></td>
        <td align="center"><B1>Order Date</B1></td>
        <td align="center"><B1>Ship By</B1></td>
        <td><B1>Part Required</B1></td>
        <td align="right"><B1>Req Qty</B1></td>
	<td align="right"><B1>O/H</B1></td>
        <td align="right"><B1>Dem.</B1></td>
        <td align="center"><B1>Job</B1></td>
        <td><B1>Stage</B1></td>
        <td align="right"><B1>Complete</B1></td>
	<td align="center"><B1>Status</B1></td>
      </tr>
      <xsl:for-each select="ExportQuery/ELEC01_SalesOrdersWaitingParts[NotEnoughStock='TRUE']">

	<xsl:if test="position()  mod 2 = 1">

      	<tr bgcolor="#CCCCCC">

	<td align="center"><B2><xsl:value-of select="Customer_CustID"/></B2></td>
        <td align="center"><xsl:value-of select="OrderHed_OrderNum"/></td>
      	<td align="center"><xsl:value-of select="OrderHed_OrderDate"/></td>
      	<td align="center"><xsl:value-of select="OrderHed_RequestDate"/></td>
      	<td><xsl:value-of select="Part_PartNum"/></td>
      	<td align="right"><xsl:value-of select="OrderDtl_OrderQty"/></td>
      	<td align="right"><xsl:value-of select="TotalOnHandQty"/></td>
      	<td align="right"><xsl:value-of select="TotalDemandQty"/></td>
      	<td align="center"><xsl:value-of select="JobHead_JobNum"/></td>
      	<td><xsl:value-of select="JobOper_OpDesc"/></td>
      	<td align="right"><xsl:value-of select="JobOper_QtyCompleted"/></td>
      	<td align="right"><xsl:value-of select="JobMtl_PartNum"/></td>

   	</tr>
	</xsl:if>


	<xsl:if test="position()  mod 2 = 0">

      	<tr bgcolor="#FFFFFF">

	<td align="center"><B2><xsl:value-of select="Customer_CustID"/></B2></td>
        <td align="center"><xsl:value-of select="OrderHed_OrderNum"/></td>
      	<td align="center"><xsl:value-of select="OrderHed_OrderDate"/></td>
      	<td align="center"><xsl:value-of select="OrderHed_RequestDate"/></td>
      	<td><xsl:value-of select="Part_PartNum"/></td>
      	<td align="right"><xsl:value-of select="OrderDtl_OrderQty"/></td>
      	<td align="right"><xsl:value-of select="TotalOnHandQty"/></td>
      	<td align="right"><xsl:value-of select="TotalDemandQty"/></td>
      	<td align="center"><xsl:value-of select="JobHead_JobNum"/></td>
      	<td><xsl:value-of select="JobOper_OpDesc"/></td>
      	<td align="right"><xsl:value-of select="JobOper_QtyCompleted"/></td>

   	</tr>
	</xsl:if>



      </xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>

This is my CHILD XSL ...

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" omit-xml-declaration="yes"/>

<xsl:template match="/">
  <html>
  <body bgcolor="white">
<B>Sub-Assemblies</B>
    <table cellpadding="4px">
      <tr>
	<td align="right"><B1>Part</B1></td>
	<td align="right"><B1>O/H</B1></td>
        <td align="right"><B1>Dem.</B1></td>
        <td align="center"><B1>Job</B1></td>
        <td><B1>Stage</B1></td>
        <td align="right"><B1>Complete</B1></td>
	<td align="center"><B1>Status</B1></td>
      </tr>
      <xsl:for-each select="ExportQuery/ELEC01_SubAssemblyWIPStock[JobHead_PartNum='document

('SOWaitingParts.xsl')/ExportQuery/ELEC01_SalesOrdersWaitingParts/JobMtl_PartNum']">

	<xsl:if test="position()  mod 2 = 1">

      	<tr bgcolor="#CCCCCC">


      	<td align="right"><xsl:value-of select="JobHead_PartNum"/></td>
      	<td align="right"><xsl:value-of select="TotalOnHandQty"/></td>
      	<td align="right"><xsl:value-of select="TotalDemandQty"/></td>
      	<td align="center"><xsl:value-of select="JobHead_JobNum"/></td>
      	<td><xsl:value-of select="JobOper_OpDesc"/></td>
      	<td align="right"><xsl:value-of select="JobOper_QtyCompleted"/></td>

   	</tr>
	</xsl:if>


	<xsl:if test="position()  mod 2 = 0">

      	<tr bgcolor="#FFFFFF">

      	<td align="right"><xsl:value-of select="JobHead_PartNum"/></td>
      	<td align="right"><xsl:value-of select="TotalOnHandQty"/></td>
      	<td align="right"><xsl:value-of select="TotalDemandQty"/></td>
      	<td align="center"><xsl:value-of select="JobHead_JobNum"/></td>
      	<td><xsl:value-of select="JobOper_OpDesc"/></td>
      	<td align="right"><xsl:value-of select="JobOper_QtyCompleted"/></td>

   	</tr>
	</xsl:if>



      </xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>

So in the first XSL you can see that it is filtered where NotEnoughStock=TRUE.

I want the CHILD data to display where

PARENT JobMtl.PartNum = CHILD JobHead.PartNum AND
PARENT NotEnoughStock = TRUE AND
CHILD Required = TRUE

I hope think makes sense.

[chriselectrix]

Polite nudge - does anyone have any suggestions?