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:
Code:
<PointOfSale>
<Contents>
<TLOG>
<Transactions>
<Transaction>
<Items>
...
</Items>
<Tenders>
...
</Tenders>
<Customer>
...
</Customer>
</Transaction>
</Transactions>
</TLOG>
</Contents>
</PointOfSale>
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.
Code:
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]
(
[Type],TrxID,Date,Register,[User],Store,Charges,Taxes,Tenders,CustID,LName,FName
)
-- SELECT stmt using OPENXML rowset provider
SELECT * FROM OPENXML (@iDoc, '/PointOfSale/Contents/TLOG/Transactions/Transaction',2)
WITH ([Type] VARCHAR(6),
[GUID] VARCHAR(25),
Date DATETIME,
RegisterCode VARCHAR(6),
[User] BIGINT,
StoreUID VARCHAR(6),
Charges MONEY,
Taxes MONEY,
Tenders MONEY,
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:
Code:
...
<IsPostVoid />
<Transaction>
<Items>
...
</Items>
<Tenders>
...
</Tenders>
<Customer>
...
</Customer>
</Transaction>
...
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!
Dealing with the presence of an XML tag rather tha
