XML -> SQL with need for cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML -> SQL with need for cursor?

Author

Topic

Mike250
Starting Member

4 Posts

Posted - 2009-02-24 : 14:22:49

Hello 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"...


<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

Mike250
Starting Member

4 Posts

Posted - 2009-02-24 : 17:02:57

I was able to solve my needs with the following query. 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources