SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 XML compare
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HappyMelon
Starting Member

6 Posts

Posted - 11/13/2013 :  13:18:26  Show Profile  Reply with Quote
Hi everyone!

I am implementing an XML compare function. I want to find out if two XML variables are equal or not. I'm choosing between three different approaches:

1) Recursively walk through and compare each node.

2) Cast the XML variables as VARCHARs and compare as strings:
CAST(@xml1 AS VARCHAR(MAX))=CAST(@xml2 AS VARCHAR(MAX))

3) Calculate and compare checksums (if possible?)

Do anyone have any ideas about what approach is best? Performance differences? The xml files are ~4kb. Any input is appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
30214 Posts

Posted - 11/13/2013 :  14:31:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you consider these two to be the same?


<a>
   <b>1</b>
   <b>2</b>
</a>

<a>
   <b>2</b>
   <b>1</b>
</a>




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

HappyMelon
Starting Member

6 Posts

Posted - 11/13/2013 :  14:41:51  Show Profile  Reply with Quote
That situation will not happen. All nodes will be unique.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30214 Posts

Posted - 11/14/2013 :  19:28:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you consider these two to be the same?

<a>
   <b>1</b>
   <c>2</c>
</a>

<a>
   <c>2</c>
   <b>1</b>
</a>



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

HappyMelon
Starting Member

6 Posts

Posted - 11/15/2013 :  01:38:01  Show Profile  Reply with Quote
No, they are not considered the same.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30214 Posts

Posted - 11/16/2013 :  07:34:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CAST as NVARCHAR(MAX) seems to be slightly faster than CAST as VARBINAR(MAX)
DECLARE	@One XML = '<a><b>1</b><c>2</c></a>',
	@Two XML = '<a><c>2</c><b>1</b></a>';

DECLARE	@Now DATETIME = GETDATE(),
	@Loop INT = 10000,
	@Dummy INT;

WHILE @Loop >= 0
	SELECT	@Dummy =	CASE
					WHEN CAST(@One AS NVARCHAR(MAX)) = CAST(@Two AS NVARCHAR(MAX)) THEN 1
					ELSE 0
				END,
		@Loop -= 1;

SELECT	DATEDIFF(MILLISECOND, @Now, GETDATE()) AS [NVARCHAR(MAX)];

SELECT	@Now = GETDATE(),
	@Loop = 10000;

WHILE @Loop >= 0
	SELECT	@Dummy =	CASE
					WHEN CAST(@One AS VARBINARY(MAX)) = CAST(@Two AS VARBINARY(MAX)) THEN 1
					ELSE 0
				END,
		@Loop -= 1;

SELECT	DATEDIFF(MILLISECOND, @Now, GETDATE()) AS [VARBINARY(MAX)];



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

HappyMelon
Starting Member

6 Posts

Posted - 11/17/2013 :  16:15:02  Show Profile  Reply with Quote
Nice. Yes, that is the solution I will go for. Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000