Hi,
   I'm stuck with one problem. Please help me to solve this.
I have two tables(xmldom objects), Inventory and Invoice.
I want to join both objects to show the May-2007 report. As like below;

[u]Invoice:</u>
InvNo Date Product Country Qty Amt
1 10-May-07 AA India 5 100
2 15-May-07 BB India 9 150
3 20-May-07 AA Sg 15 200
4 24-May-07 AA India 10 150
5 05-Jun-07 AA India 5 100


[u]Inventory:</u>
Month Product Country Qty Amt
May-07 AA India 100 500
May-07 AA UK 50 300
May-07 BB UK 90 200
May-07 BB India 99 300
Jun-07 ....

[u]Expected Output:</u>
Country Month Product InvoiceQty InventQty
India May-07 AA Sum(InvcQty) InvtQty

Conditions based on the month and country and product
using xpath 1.0 in .Net 2.0 and javascript.

Pls help me to solve this...,
Thanks in Advance


viv

__________________
vivek

It would be easier if you showed your inputs in XML, since the XPath expression will have to refer to them that way.

Your output is shown as an ASCII table. Presumably you want the output to be XML as well? XPath cannot construct new XML trees, it can only select nodes from existing trees. To construct new nodes you need XSLT (or XQuery if you prefer).

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

Well the for sum you could use a XPath expression similar to this:

"sum(invoice[product='AA' and substring-after(date, '-')='May-07' and country='India']/quantity)";

If you take the variables you need from one and use them in this.

It would depend greatly on how much information you have, but it might be easier to transform the invoice data first, then do the lookups.

/- Sam Judson : Wrox Technical Editor -/

Hi Sam,
        I got an error when i applied using javascript;

xmlNode = xml.selectNodes("sum(invoice[Product='AA' and substring-after(Date, '-')='May-07' and Country='India']/quantity)");

Error: Expression does not return a DOM node
-->sum(invoice[Product....]/quantity)<--

Thanks in Advance

viv

That's because that xpath doesn't return a node, just a value. Not sure how you get access to that using javascript. You could miss out the sum() and add the results manually.

/- Sam Judson : Wrox Technical Editor -/

Hi,
    Thank you Sam! I'm a beginner.., If javascript doesn't work out then pls help me to write an xslt for this to get the expected output..,

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventry.xsd" generated="2007-11-09T16:24:41">
<Inventry>
<ID>1</ID>
<Month>May</Month>
<Product>HH</Product>
<Country>India</Country>
<Qty>100</Qty>
</Inventry>
<Inventry>
<ID>2</ID>
<Month>May</Month>
<Product>AA</Product>
<Country>India</Country>
<Qty>200</Qty>
</Inventry>
<Inventry>
<ID>3</ID>
<Month>Jun</Month>
<Product>BB</Product>
<Country>Aus</Country>
<Qty>150</Qty>
</Inventry>
<Inventry>
....
</dataroot>

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Invoice.xsd" generated="2007-11-09T16:24:59">
<Invoice>
<InvoiceNo>1</InvoiceNo>
<Date>2007-05-10T00:00:00</Date>
<Product>HH</Product>
<Country>India</Country>
<Qty>10</Qty>
<Amt>200</Amt>
</Invoice>
<Invoice>
<InvoiceNo>2</InvoiceNo>
<Date>2007-05-10T00:00:00</Date>
<Product>HH</Product>
<Country>India</Country>
<Qty>5</Qty>
<Amt>100</Amt>
</Invoice>
<Invoice>
<InvoiceNo>3</InvoiceNo>
<Date>2007-05-10T00:00:00</Date>
<Product>AA</Product>
<Country>India</Country>
<Qty>10</Qty>
<Amt>300</Amt>
</Invoice>
...
</dataroot>


Thanks in Advance

viv

I'm assuming you want one row in the output for every row in the Inventory table. So it's best to make Inventory.xml the primary input of your transformation. I'll assume first that your Inventory file had the month in the format "2007-05". The essence of it is then:

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

<xsl:template match="Inventry">
  <row>
    <xsl:copy-of select="Country"/>
    <xsl:copy-of select="Month"/>
    <xsl:copy-of select="Product"/>
    <InvoiceQty><xsl:value-of select="sum($invoices/*[starts-with(Date, current()/Month]/Qty)"/></InvoiceQty>
    <xsl:copy-of select="Qty"/>
  </row>
</xsl:template>

You need to do a bit more work because you need to translate the month from "May" to "2007-05". I'm not sure where you want to get the year from. You can do the month part as

format-number(string-length(substring-before("JanFebMarApr...Dec", $month)) div 3 + 1, '00')

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

Hi Michael,
        I have tried using the code, but the Sum(InvoiceQty) returns always "0". I have changed both the date format uniform as 2007-05,2007-06..,. But still it's returning "0".

But I have experimented one thing as like below;
<xsl:value-of select="$invoices/Qty"/> returns "0"
<xsl:value-of select="$invoices/*"/> returns the whole Invoice.xml.

Is there anything I missed out?

Javascript code:
        function loadXmlXsl(){
            // Load XML
            var xml = new ActiveXObject("Microsoft.XMLDOM")
            xml.async = false
            xml.load("Inventry.xml")
            // Load XSL
            var xsl = new ActiveXObject("Microsoft.XMLDOM")
            xsl.async = false
            xsl.load("Inventry.xsl")
            // Transform
            alert(xml.transformNode(xsl));
            document.write(xml.transformNode(xsl))
        }

Pls help me to get Sum(InvoiceQty) for the month of assume 2007-05.

Thank you


viv

Shouldn't that be --

Joe (Microsoft MVP - XML)

Show us your input files in XML, and your full stylesheet, and I'm sure we'll be able to find the bug quickly.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference