How to do XML - XML Conversion with an XSLT, using grouping? 1

[jimbo14]

Hi People,

I want to transform this XML document:

<?xml version="1.0" encoding="UTF-8"?>
<notification-history-response xmlns="

http://checkout.google.com/schema/2"

serial-number="ca8575fb-fa68-4657-bd67-9c65be145a84">
<notifications>
<order-state-change-notification serial-number="372522188722936-00006-1">
<timestamp>2009-02-17T11:21:12.977Z</timestamp>
<google-order-number>372522188722936</google-order-number>
<new-fulfillment-order-state>NEW</new-fulfillment-order-state>
<new-financial-order-state>CHARGEABLE</new-financial-order-state>
<previous-fulfillment-order-state>NEW</previous-fulfillment-order-state>
<previous-financial-order-state>REVIEWING</previous-financial-order-state>
</order-state-change-notification>
<order-state-change-notification serial-number="372522188722936-00007-1">
<timestamp>2009-02-25T14:05:58.385Z</timestamp>
<google-order-number>372522188722936</google-order-number>
<new-fulfillment-order-state>WILL_NOT_DELIVER</new-fulfillment-order-state>
<new-financial-order-state>CANCELLED</new-financial-order-state>
<previous-fulfillment-order-state>NEW</previous-fulfillment-order-state>
<previous-financial-order-state>CHARGEABLE</previous-financial-order-state>
</order-state-change-notification>
<order-state-change-notification serial-number="635864704530878-00005-1">
<timestamp>2009-02-17T16:50:15.598Z</timestamp>
<google-order-number>635864704530878</google-order-number>
<new-fulfillment-order-state>NEW</new-fulfillment-order-state>
<new-financial-order-state>CHARGEABLE</new-financial-order-state>
<previous-fulfillment-order-state>NEW</previous-fulfillment-order-state>
<previous-financial-order-state>REVIEWING</previous-financial-order-state>
</order-state-change-notification>
<order-state-change-notification serial-number="635864704530878-00006-1">
<timestamp>2009-02-25T14:05:19.990Z</timestamp>
<google-order-number>635864704530878</google-order-number>
<new-fulfillment-order-state>WILL_NOT_DELIVER</new-fulfillment-order-state>
<new-financial-order-state>CANCELLED</new-financial-order-state>
<previous-fulfillment-order-state>NEW</previous-fulfillment-order-state>
<previous-financial-order-state>CHARGEABLE</previous-financial-order-state>
</order-state-change-notification>
<order-state-change-notification serial-number="745942158683282-00004-1">
<timestamp>2009-02-23T17:05:31.658Z</timestamp>
<google-order-number>745942158683282</google-order-number>
<new-fulfillment-order-state>WILL_NOT_DELIVER</new-fulfillment-order-state>
<new-financial-order-state>CANCELLED</new-financial-order-state>
<previous-fulfillment-order-state>NEW</previous-fulfillment-order-state>
<previous-financial-order-state>REVIEWING</previous-financial-order-state>
</order-state-change-notification>
</notification-history-response>

..... into THIS XML document:

<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
<ROW>
<GOOGLE_ORDER_NO>372522188722936</GOOGLE_ORDER_NO>
<ORDER_STATE>CANCELLED</ORDER_STATE>
<DATE_UPDATED>2009-02-25T14:05:58.385Z</DATE_UPDATED>
</ROW>
<ROW>
<GOOGLE_ORDER_NO>635864704530878</GOOGLE_ORDER_NO>
<ORDER_STATE>CANCELLED</ORDER_STATE>
<DATE_UPDATED>2009-02-25T14:05:19.990Z</DATE_UPDATED>
</ROW>
<ROW>
<GOOGLE_ORDER_NO>745942158683282</GOOGLE_ORDER_NO>
<ORDER_STATE>CANCELLED</ORDER_STATE>
<DATE_UPDATED>2009-02-23T17:05:31.658Z</DATE_UPDATED>
</ROW>
</ROWSET>

Basically, I want to ensure that each "google-order-number" element is sorted in ascending order of "timestamp", and then take the latest "new-financial-order-state" and "timestamp" element values for each Google Order Number, and transfer them to ORDER_STATE and DATE_UPDATED in the new XML document.

Essentially, I'm trying to group the output on "google-order-number", and am only interested in the "new-financial-order-state" associated with the latest "timestamp".

I came up with the follwing XSLT to do the sorting:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="

http://www.w3.org/1999/XSL/Transform"

version="1.0" xmlns:gc="

http://checkout.google.com/schema/2">

<xslutput method="xml"/>
<xsl:template match="gc:notification-history-response/gc:notifications">
<xsl:for-each select="gcrder-state-change-notification" order-by="+gc:google-order-number; +gc:timestamp">
<ROWSET>
<!-- Unfortunately, after spending the last 3 hours buried in books and on the web,
I've not been able to figure this next bit out.
-->
</ROWSET>
</xsl:template>
</xsl:stylesheet>

Unfortunately, try as I might, I've not been able to figure out what should go where the comments are. I've developed a reasonable number of basic XSL stylesheets, but this is a tad more involved.

Any help or suggestions would be appreciated.

Cheers.

James

 

[tsuji]

[0] I would suggest you ask oracle forum for specifics related to their xslt processor. According to your other thread, it is "unusual".

[1] For xslt v1.0, it is the fairly standard use of xsl:key. Standard in a sense, it still requires some skill on a case-by-case basis. This is how. (Any further variations, it is on your own because they are just varying adaptation of the same approach.)
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="[ignore]

http://www.w3.org/1999/XSL/Transform

[/ignore]"
xmlns:gc="[ignore]

http://checkout.google.com/schema/2

[/ignore]"
>
<xslutput method="xml" omit-xml-declaration="no" indent="yes" encoding="utf-8" />
<xsl:key name="gonkey"
match="gcrder-state-change-notification"
use="gc:google-order-number"
/>

<xsl:template match="/">
<ROWSET>
<xsl:apply-templates select="gc:notification-history-response" />
</ROWSET>
</xsl:template>

<xsl:template match="gc:notification-history-response">
<xsl:apply-templates select="gc:notifications" />
</xsl:template>

<xsl:template match="gc:notifications">
<xsl:for-each select="gcrder-state-change-notification[count(.|key('gonkey',gc:google-order-number)[1])=1]">
<xsl:sort select="gc:google-order-number" order="ascending" />
<xsl:for-each select="key('gonkey',gc:google-order-number)">
<xsl:sort select="gc:timestamp" order="descending" />
<xsl:if test="position()=1">
<ROW>
<GOOGLE_ORDER_NO><xsl:value-of select="gc:google-order-number" /></GOOGLE_ORDER_NO>
<ORDER_STATE><xsl:value-of select="gc:new-financial-order-state" /></ORDER_STATE>
<DATE_UPDATE><xsl:value-of select="gc:timestamp" /></DATE_UPDATE>
</ROW>
</xsl:if>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>[/tt]

 

[jimbo14]

Thank tsuji. I'll have a play with that later.

I'd got around by loading every "order-state-change-notification" element into an Oracle Global Temporary tables (contents stored in memory), and then using SQL to do the grouping and eliminate the redundant state-change elements.

I'd like to do it in the xslt if possible though, so I will come back to this later. I'd rather supplement my XSL knowledge rather than just sticking to the things I know well (Oracle SQL and PL/SQL), so will look on this as a learning opportunity.