USE PROD
GO


  CREATE FUNCTION [dbo].[CompareXml]
(
    @xml1 XML,
    @xml2 XML
)
RETURNS INT
AS
BEGIN
    DECLARE @ret INT
    SELECT @ret = 0


    -- -------------------------------------------------------------
    -- If one of the arguments is NULL then we assume that they are
    -- not equal.
    -- -------------------------------------------------------------
    IF @xml1 IS NULL OR @xml2 IS NULL
    BEGIN
        RETURN 1
    END

    -- -------------------------------------------------------------
    -- Match the name of the elements
    -- -------------------------------------------------------------
    IF  (SELECT @xml1.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
        <>
        (SELECT @xml2.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
    BEGIN
        RETURN 1
    END

     ---------------------------------------------------------------
     --Match the value of the elements
     ---------------------------------------------------------------
    IF((@xml1.query('count(/*)').value('.','INT') = 1) AND (@xml2.query('count(/*)').value('.','INT') = 1))
    BEGIN
    DECLARE @elValue1 VARCHAR(MAX), @elValue2 VARCHAR(MAX)

    SELECT
        @elValue1 = @xml1.value('((/*)[1])','VARCHAR(MAX)'),
        @elValue2 = @xml2.value('((/*)[1])','VARCHAR(MAX)')

    IF  @elValue1 <> @elValue2
    BEGIN
        RETURN 1
    END
    END

    -- -------------------------------------------------------------
    -- Match the number of attributes
    -- -------------------------------------------------------------
    DECLARE @attCnt1 INT, @attCnt2 INT
    SELECT
        @attCnt1 = @xml1.query('count(/*/@*)').value('.','INT'),
        @attCnt2 = @xml2.query('count(/*/@*)').value('.','INT')

    IF  @attCnt1 <> @attCnt2 BEGIN
        RETURN 1
    END


    -- -------------------------------------------------------------
    -- Match the attributes of attributes
    -- Here we need to run a loop over each attribute in the
    -- first XML element and see if the same attribut exists
    -- in the second element. If the attribute exists, we
    -- need to check if the value is the same.
    -- -------------------------------------------------------------
    DECLARE @cnt INT, @cnt2 INT
    DECLARE @attName VARCHAR(MAX)
    DECLARE @attValue VARCHAR(MAX)

    SELECT @cnt = 1

    WHILE @cnt <= @attCnt1
    BEGIN
        SELECT @attName = NULL, @attValue = NULL
        SELECT
            @attName = @xml1.value(
                'local-name((/*/@*[sql:variable("@cnt")])[1])',
                'varchar(MAX)'),
            @attValue = @xml1.value(
                '(/*/@*[sql:variable("@cnt")])[1]',
                'varchar(MAX)')

        -- check if the attribute exists in the other XML document
        IF @xml2.exist(
                '(/*/@*[local-name()=sql:variable("@attName")])[1]'
            ) = 0
        BEGIN
            RETURN 1
        END

        IF  @xml2.value(
                '(/*/@*[local-name()=sql:variable("@attName")])[1]',
                'varchar(MAX)')
            <>
            @attValue
        BEGIN
            RETURN 1
        END

        SELECT @cnt = @cnt + 1
    END

    -- -------------------------------------------------------------
    -- Match the number of child elements
    -- -------------------------------------------------------------
    DECLARE @elCnt1 INT, @elCnt2 INT
    SELECT
        @elCnt1 = @xml1.query('count(/*/*)').value('.','INT'),
        @elCnt2 = @xml2.query('count(/*/*)').value('.','INT')


    IF  @elCnt1 <> @elCnt2
    BEGIN
        RETURN 1
    END


    -- -------------------------------------------------------------
    -- Start recursion for each child element
    -- -------------------------------------------------------------
    SELECT @cnt = 1
    SELECT @cnt2 = 1
    DECLARE @x1 XML, @x2 XML
    DECLARE @noMatch INT

    WHILE @cnt <= @elCnt1
    BEGIN

        SELECT @x1 = @xml1.query('/*/*[sql:variable("@cnt")]')
    --RETURN CONVERT(VARCHAR(MAX),@x1)
    WHILE @cnt2 <= @elCnt2
    BEGIN
        SELECT @x2 = @xml2.query('/*/*[sql:variable("@cnt2")]')
        SELECT @noMatch = dbo.CompareXml( @x1, @x2 )
        IF @noMatch = 0 BREAK
        SELECT @cnt2 = @cnt2 + 1
    END

    SELECT @cnt2 = 1

        IF @noMatch = 1
        BEGIN
            RETURN 1
        END

        SELECT @cnt = @cnt + 1
    END

    RETURN @ret
END

GO


  DECLARE @ExcludeMerchantsList TABLE(MerchantCodeId CHAR(4));

  INSERT INTO @ExcludeMerchantsList
  VALUES
('1066'),
('1501'),
('1500'),
('1502'),
('1503'),
('1504'),
('1505'),
('1506'),
('1507'),
('1508'),
('1509'),
('1510'),
('1511'),
('1512'),
('1513'),
('1514'),
('1515'),
('1516'),
('1517'),
('1518'),
('1519'),
('1520'),
('1521'),
('1522'),
('1523'),
('1524'),
('1525'),
('1526'),
('1527'),
('1528'),
('2011'),
('4001'),
('4438'),
('4449'),
('4550'),
('4552'),
('4557'),
('4580'),
('5108'),
('5158'),
('5172'),
('5173'),
('5188'),
('5647'),
('5736'),
('6015'),
('6073');



  DECLARE @FullAccessExpressFilters NVARCHAR(MAX) = '<Filters><FilterCountries><Add ID="*"></Add></FilterCountries><FilterBanks><Add ID="*"></Add></FilterBanks><FilterCurrencies><Add ID="*"></Add></FilterCurrencies><FilterChannels><Add ID="*"></Add></FilterChannels></Filters>';


  SELECT [MerchantId], [m].[MERCHANT_ID], [XMLConfiguration]
  FROM [dbo].[MerchantSetup] [ms]
  JOIN [dbo].[Merchants] [m] ON [m].[Id] = [ms].[MerchantId]
  WHERE CAST(CONVERT(NVARCHAR(MAX), REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16')) AS XML).value('(/Configuration/DirectConfiguration/@enabled)[1]', 'Nvarchar(max)') = 'true'
  AND [ActiveConfiguration] = 1
  AND datalength(CAST(CONVERT(NVARCHAR(MAX), REPLACE(REPLACE(REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16'), '<Parameters/>', ''), '<Parameters />', '')) AS XML).query('/Configuration/DirectConfiguration/Filters')) = 5 -- Empty
  AND dbo.CompareXml(@FullAccessExpressFilters, CAST(CAST(CONVERT(NVARCHAR(MAX), REPLACE(REPLACE(REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16'), '<Parameters/>', ''), '<Parameters />', '')) AS XML).query('/Configuration/Filters') AS NVARCHAR(MAX))) > 0
  AND [m].MERCHANT_ALLOWED = 1
  AND [m].MERCHANT_ACTIVE = 1
  AND [XMLConfiguration] IS NOT NULL
  AND [m].[MERCHANT_ID] NOT IN (SELECT MerchantCodeId FROM @ExcludeMerchantsList)
  AND [XMLConfiguration] LIKE '%DirectConfiguration%enabled="true"%'

UNION

  SELECT [MS].[MerchantId], [m].[MERCHANT_ID], [XMLConfiguration]

  FROM [dbo].[MerchantSetup] [ms]
  JOIN [dbo].[Merchants] [m] ON [m].[Id] = [ms].[MerchantId]
  WHERE CAST(CONVERT(NVARCHAR(MAX), REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16')) AS XML).value('(/Configuration/DirectConfiguration/@enabled)[1]', 'NVARCHAR(MAX)') = 'true'
  AND [ActiveConfiguration] = 1
  AND datalength(CAST(CONVERT(NVARCHAR(MAX), REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16')) AS XML).query('/Configuration/DirectConfiguration/Filters')) <> 5 -- not empty
  AND dbo.CompareXml(
    CAST(CAST(CONVERT(NVARCHAR(MAX), REPLACE(REPLACE(REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16'), '<Parameters/>', ''), '<Parameters />', '')) AS XML).query('/Configuration/Filters') AS NVARCHAR(MAX)),
    CAST(CAST(CONVERT(NVARCHAR(MAX), REPLACE(REPLACE(REPLACE([XMLConfiguration], 'UTF-8', 'UTF-16'), '<Parameters/>', ''), '<Parameters />', '')) AS XML).query('/Configuration/DirectConfiguration/Filters') AS NVARCHAR(MAX))) > 0
AND [m].MERCHANT_ALLOWED = 1
AND [m].MERCHANT_ACTIVE = 1
AND [XMLConfiguration] IS NOT NULL
AND [m].[MERCHANT_ID] NOT IN (SELECT MerchantCodeId FROM @ExcludeMerchantsList)
AND [XMLConfiguration] LIKE '%DirectConfiguration%enabled="true"%'

ORDER BY [MS].[MerchantId];

GO

DROP FUNCTION [dbo].[CompareXml];