I'm having a head banging moment where I am sure the answer is painfully obvious but I just can't seem to get there. First, some context:
Retail Transaction log in XML being sucked into an SQL Server 2005 database. At it's simplest the TLog looks something like:
Following I have a code snippet to check starting at an expected node and building what is my transaction header table; essentially specific information pertaining to the transaction. I'm not having any problems doing this when the start and end tags each have data within them.
DECLARE @iDoc INT
DECLARE @xmlDoc XML
SET @xmlDoc = (SELECT * FROM OPENROWSET ( BULK 'C:\tlog.xml', SINGLE_CLOB ) AS xmlData)
--Prepare the XML Document by executing a system stored procedure
EXEC SP_XML_PREPAREDOCUMENT @iDoc OUTPUT, @xmlDoc
-- INSERT stmt for header table
INSERT INTO [Header_Table]
-- SELECT stmt using OPENXML rowset provider
SELECT * FROM OPENXML (@iDoc, '/PointOfSale/Contents/TLOG/Transactions/Transaction',2)
WITH ([Type] VARCHAR(6),
SyncID VARCHAR(25) 'Customer/SyncID',
NameLast VARCHAR(25) 'Customer/NameLast',
NameFirst VARCHAR(25) 'Customer/NameFirst'
WHERE NOT EXISTS (SELECT * FROM [Header_Table] WHERE TrxID = [GUID])
-- Free up the memory used by the XML document.
EXEC SP_XML_REMOVEDOCUMENT @iDoc
My problem starts when I have tags with no data; in fact ending tags with no start tags and no data. It looks like this:
The presence of the tag <IsPostVoid /> denotes this is a void transaction, so I need to record into some field the transaction "Mode" if you like. I'll create a new field on my [Header_Table] and call it Mode and would like to store in this field the word "Post Void" if <IsPostVoid /> exists in a transaction.
I'm stumped though. Ideas? Thank you in advance!