View Single Post
  #1 (permalink)  
Old May 19th, 2008, 09:25 AM
Mike250 Mike250 is offline
Registered User
 
Join Date: May 2008
Location: , , Australia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Mike250
Default Dealing with the presence of an XML tag rather tha

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!




Reply With Quote