Quick look at this query will show that it uses the anti-pattern of feeding a high cardinality result set into the distinct operator, hence the high estimation in the plan....

Thank you for the feedback and if you have any further questions on XML then don't hesitate coming back here 😉
😎

Quick thought, you should use the...

Since the encoding in the header is marked as utf-8, you will have to convert the nvarchar to varchar before converting to XML, SQL Server cannot switch the encoding implicitly.

I have done some fairly extensive tests on the string splitting options and the JSON split methods and the STRING_SPLIT functions are by far the best performers if one is...

Piling on, can you post a proper example of the XML data please, the data you've posted is ill-formed.
😎

If you are on SQL Server 2016 then use the new native STRING_SPLIT function.
😎

IF..ELSE are conditional batch execution directives, not a conditional evaluation like CASE, hence the former is not valid within a select statement which in it self is a...