Auto Number with Reset.

[aswolff]

Hello.

I have the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
    <TABLE>
        <ROW>
            <RECKEY>500011802322</RECKEY>
            <COMPANY>5000</COMPANY>
            <CUSTOMER>1180</CUSTOMER>
            <TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
            <ENTERED_PRICE>11802.38</ENTERED_PRICE>
        </ROW>
        <ROW>
            <RECKEY>500011802322</RECKEY>
            <COMPANY>5000</COMPANY>
            <CUSTOMER>1180</CUSTOMER>
            <TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
            <ENTERED_PRICE>5244.06</ENTERED_PRICE>
        </ROW>
        <ROW>
            <RECKEY>500011802322</RECKEY>
            <COMPANY>5000</COMPANY>
            <CUSTOMER>1180</CUSTOMER>
            <TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
            <ENTERED_PRICE>5244.06</ENTERED_PRICE>
        </ROW>
        <ROW>
            <RECKEY>500011802324</RECKEY>
            <COMPANY>5000</COMPANY>
            <CUSTOMER>1180</CUSTOMER>
            <TM_INVOICE_NUMBER>2324</TM_INVOICE_NUMBER>
            <ENTERED_PRICE>5244.06</ENTERED_PRICE>
        </ROW>
    </TABLE>
</ROOT>

I want to add a <LINE_NBR> node which resets back to 1 for each change in the <RECKEY> to give:

<ROOT>
<TABLE>
<ROW>
<RECKEY>500011802322</RECKEY>
<COMPANY>5000</COMPANY>
<CUSTOMER>1180</CUSTOMER>
<TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
<ENTERED_PRICE>11802.38</ENTERED_PRICE>
<LINE_NBR>1</LINE_NBR>
</ROW>
<ROW>
<RECKEY>500011802322</RECKEY>
<COMPANY>5000</COMPANY>
<CUSTOMER>1180</CUSTOMER>
<TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
<ENTERED_PRICE>5244.06</ENTERED_PRICE>
<LINE_NBR>2</LINE_NBR>
</ROW>
<ROW>
<RECKEY>500011802322</RECKEY>
<COMPANY>5000</COMPANY>
<CUSTOMER>1180</CUSTOMER>
<TM_INVOICE_NUMBER>2322</TM_INVOICE_NUMBER>
<ENTERED_PRICE>5244.06</ENTERED_PRICE>
<LINE_NBR>3</LINE_NBR>
</ROW>
<ROW>
<RECKEY>500011802324</RECKEY>
<COMPANY>5000</COMPANY>
<CUSTOMER>1180</CUSTOMER>
<TM_INVOICE_NUMBER>2324</TM_INVOICE_NUMBER>
<ENTERED_PRICE>5244.06</ENTERED_PRICE>
<LINE_NBR>1</LINE_NBR>
</ROW>
</TABLE>
</ROOT>
[/code]


I am guessing I need to use some variant of the number() function and perhaps a key.

Any help is appreciated.

 

[tsuji]

... and I have tried...