Thursday, April 8, 2021

SQL XML String Splitter

I don't use this method often enough to memorize it, so I'm making a bookmark for MSSQL Tips article on how to implement. One trick to it is that I found that the ampersand character "&" will break the XML parsing. The workaround to this is substituting another rarely found character for it before parsing, then reverse the substitution afterwards. This StackOverflow post goes into some detail about this.

Check out this site for detailed analysis of how XML string parsing compares to iterative method.

And finally, to raise this even higher, Brent Ozar has a great article about using the STRING_SPLIT function in SQL 2016 and higher, which simplifies this process immensely (although getting it to output parsed element numbers adds a layer of complexity). Check out this StackOverflow post on how to add those element numbers. Beware that although they will be consecutive, they may not start with "1", which means another pass to recalibrate.

No comments:

Post a Comment