Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HappyMelon
Starting Member
6 Posts |
 Posted - 2013-11-13 : 13:18:26
|
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
30421 Posts |
Posted - 2013-11-13 : 14:31:14
|
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 |
 |
|
|
HappyMelon
Starting Member
6 Posts |
Posted - 2013-11-13 : 14:41:51
|
| That situation will not happen. All nodes will be unique. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-11-14 : 19:28:36
|
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 |
|
|
|
HappyMelon
Starting Member
6 Posts |
Posted - 2013-11-15 : 01:38:01
|
| No, they are not considered the same. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-11-16 : 07:34:46
|
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 |
|
|
|
HappyMelon
Starting Member
6 Posts |
Posted - 2013-11-17 : 16:15:02
|
| Nice. Yes, that is the solution I will go for. Thanks |
|
|
|
|
|
|
|
|
XML compare