Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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%'
- 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%'
- ORDER BY [MS].[MerchantId];
- GO
- DROP FUNCTION [dbo].[CompareXml];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement