p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2005 (http://p2p.wrox.com/forumdisplay.php?f=220)
-   -   Dealing with the presence of an XML tag rather tha (http://p2p.wrox.com/showthread.php?t=68215)

Mike250 May 19th, 2008 09:25 AM

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!





SQLScott June 6th, 2008 03:50 PM

Did you get this solved? I can take a look at it for you if you'd like...

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================


All times are GMT -4. The time now is 08:50 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.