February 24, 2009 at 12:45 pm
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
February 24, 2009 at 3:03 pm
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