XML -> SQL with need for cursor?

  • G'day all,

    I've got a current business project which involves importing an XML file of retail transaction log data into SQL Server 2005. I've performed one previous project of similar nature so I'm not exactly a guru when it comes to XML to SQL. The way I did it before and therefore doing now is by bulk loading the XML file with OPENROWSET and then using OPENXML to select the nodes I want and populate the tables I need. Not sure if it's the best method but worked well last time.

    I'm stumped on one particular issue and was hoping talking it out might get some feedback in ideas to handle it. This retail organization has cashiers that sign on to their register, and every transaction that follows up until the next cashier sign on belongs to that cashier. In the XML file I've been given I do not get a Cashier number for every transaction, so I need to come up with a way to populate it down from the initial sign on transaction into all transactions below it up until the next cashier sign on.

    The XML looks like this, assume additional transactions occur afterwards for CasheirNo="114"... (also, replaced tags with [ ] so that it would show up in the code box below).

    [Root]

    [Events]

    [CasheirSignOn Opcode="32" CasheirNo="114" TicketNumber="1" Time="07:14:06" PosNo="1" StoreNumber="0105" /]

    [/Events]

    [Ticket]

    [TicketStart Opcode="33" TicketNumber="1" Time="07:30:41" PosNo="1" StoreNumber="0105" /]

    [PluSale Opcode="1" PluCode="00000000037025" Qty="1" Price="169" Amount="169" TicketNumber="1" Time="07:30:41" PosNo="1" StoreNumber="0105" /]

    [PluSale Opcode="1" PluCode="00000000019000" Qty="1" Price="145" Amount="145" TicketNumber="1" Time="07:30:44" PosNo="1" StoreNumber="0105" /]

    [PluSale Opcode="1" PluCode="00000000040120" Qty="1" Price="0" Amount="0" TicketNumber="1" Time="07:30:47" PosNo="1" StoreNumber="0105" /]

    [Media Opcode="4" MediaNo="4" Amount="335" TicketNumber="1" Time="07:31:09" PosNo="1" StoreNumber="0105" /]

    [TicketEnd Opcode="5" ItemsNo="3" TicketAmount="335" TicketNumber="1" Time="07:31:12" PosNo="1" StoreNumber="0105" /]

    [/Ticket]

    [/Root]

    I built a temporary table to house each occurrence of Cashier sign on, figuring I might be able to cross reference or perhaps build a CURSOR. It looks like this for one store, 2 registers on one day:

    Cashier Sign On Table

    OpCode CashierNo TicketNumber Time PosNo StoreNumber

    32 114 1 07:14:06 1 0105

    32 106 96 15:40:22 1 0105

    32 106 157 22:30:55 1 0105

    32 113 1 11:10:36 2 0105

    32 113 78 14:19:43 2 0105

    32 120 78 15:52:10 2 0105

    The assumption you can make above is that Cashier 114 on PosNo 1 owns transactions 1 through 95. Cashier 106 therefore owns transactions 96 through 156, and then 157 which happens to be the last transaction of the day, probably an end shift function, though I don't get that detail. Just an additional sign on transaction.

    The table I want to populate with Cashier No is the Ticket_Header table, which contains information on when each transaction starts. A snippet of the table is below, including the empty CashierNo field waiting to be populated with the correct Cashier.

    Transaction Header Line table

    OpCode TicketNumber Time PosNo StoreNumber CashierNo

    33 1 07:30:41 1 0105 NULL

    33 2 07:55:52 1 0105 NULL

    33 3 08:03:47 1 0105 NULL

    .. .. ........ . .... ....

    33 97 15:41:29 1 0105 NULL

    33 98 15:42:12 1 0105 NULL

    33 99 15:48:33 1 0105 NULL

    I'll also need to populate an additional Ticket_Details table, which contains SK'Us, Quantity's and Amounts, but finding a solution here I should be able to take that there.

    I originally tried stepping back through the XML elements with something like the following:

    SELECT * FROM OPENXML (@iDoc, '/Root/Ticket/TicketStart', 2)

    WITH (

    Opcode VARCHAR(10) '@Opcode',

    TicketNumber VARCHAR(10) '@TicketNumber',

    [Time] VARCHAR(10) '@Time',

    PosNo VARCHAR(10) '@PosNo',

    [Unique] VARCHAR(10) '@Unique',

    TV VARCHAR(10) '@TV',

    StoreNumber VARCHAR(10) '@StoreNumber',

    CashierNo VARCHAR(10) '../../Events/CasheirSignOn/@CasheirNo'

    ) AS tbl

    but all it did was populate the value 114 for every record in the table.

    I started going down the CURSOR route and came up with a procedure, but couldn't figure out logic to assign the right cashier to the right records. So far it looks like this:

    CREATE PROCEDURE Cashier

    AS

    DECLARE RawCursor CURSOR FAST_FORWARD FOR

    SELECT

    CashierNo,

    TicketNumber,

    [Time],

    PosNo,

    StoreNumber

    FROM Ticket_CashierSignOn

    DECLARE

    @CashierNo VARCHAR(50), @TicketNumber VARCHAR(50), @Time VARCHAR(50),

    @PosNo VARCHAR(50), @StoreNumber VARCHAR(50)

    OPEN RawCursor

    WHILE 0=0 BEGIN

    FETCH NEXT FROM RawCursor INTO @CashierNo, @TicketNumber, @Time, @PosNo, @StoreNumber

    IF @@FETCH_STATUS <> 0 BREAK

    UPDATE Ticket_Start

    SET

    CashierNo = @CashierNo

    WHERE

    PosNo = @PosNo AND

    StoreNumber = @StoreNumber AND

    TicketNumber >= @TicketNumber AND

    TicketNumber < {some formula to figure out when to stop}

    END

    CLOSE RawCursor

    DEALLOCATE RawCursor

    GO

    I got discouraged and started looking elsewhere for ideas. Here I am. I apologize for a lot of info, but I want to be thorough. Cheers for any advice or suggestions!

    Michael

  • I was able to solve this with the following query. A classic example of over thinking this whole thing. Thanks for your ear!

    SELECT *,

    (SELECT

    TOP 1 CashierNo

    FROM Ticket_CashierSignOn

    WHERE

    CONVERT(INT, TicketNumber) <= CONVERT(INT, Ticket_Start.TicketNumber) AND

    StoreNumber = Ticket_Start.StoreNumber AND

    PosNo = Ticket_Start.PosNo

    ORDER BY TicketNumber DESC

    )

    AS CashierNo

    FROM Ticket_Start

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply