CREATE TABLE #MoneyTable
            (
              Id INT IDENTITY(1, 1)
                     PRIMARY KEY ,
              MoneyName VARCHAR(50) ,
              Cents INT
            )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘UnSupported‘, 0 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarOne‘, 100 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarTwo‘, 200 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarFive‘, 500 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarTen‘, 1000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarTwenty‘, 2000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarFifty‘, 5000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘DollarHundred‘, 10000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘CentOne‘, 1 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘CentFive‘, 5 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘CentTen‘, 10 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘CentTwentyFive‘, 25 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( ‘CentFifty‘, 50 )      
            
         
        SELECT  id ,
                Payload.value(‘(TransactionJournal/TransactionDetail/BillBreakTransactionDetail/BillBreakDetail/MoneyDenom/denom)[1]‘,
                              ‘varchar(50)‘) AS ‘MoneyName‘ ,
                Payload.value(‘(TransactionJournal/TransactionDetail/Amount)[1]‘,
                              ‘int‘) AS ‘Cents‘ ,
                Payload.value(‘(TransactionJournal/TransactionDualDetail/BillBreakTransactionDetail/BillBreakDetail/MoneyDenom/denom)[1]‘,
                              ‘varchar(50)‘) AS ‘DualMoneyName‘ ,
                Payload.value(‘(TransactionJournal/TransactionDualDetail/Amount)[1]‘,  --2.Search the specified node in the specified field
                              ‘int‘) AS ‘DualCents‘
        INTO    #tempJournal   --1.Don‘t to state create #tempJournal
        FROM    Kiosk.Journal
        WHERE   ModuleTypeId = 3
        
       
       

        SELECT  *
        FROM    Kiosk.Journal
        WHERE   id NOT IN ( SELECT  t1.id
                            FROM    #tempJournal t1 ,
                                    #MoneyTable t2
                            WHERE   ( t1.MoneyName = t2.MoneyName
                                      AND t1.Cents = t2.Cents
                                      AND t1.DualMoneyName IS NULL
                                      AND t1.DualCents IS NULL
                                    )
                                    OR ( t1.DualMoneyName = t1.MoneyName
                                         AND t1.DualCents = t2.Cents
                                         AND t1.MoneyName IS NULL
                                         AND t1.Cents IS NULL
                                       ) )
                AND ModuleTypeId = 3

        DROP TABLE #MoneyTable
        DROP TABLE #tempJournal