Recently I was asked how to parse text out of an HTML fragment stored in SQL Server.
Over the next few seconds my brain processed the following ideas:
SQL Server is not meant for parsing HTML. Parse the data with something else.
T-SQL does have functions like REPLACE, CHARINDEX, and SUBSTRING though, perfect for searching for tags and returning just the values between them.
CLRs could do it, probably using some kind of HTML shredding library. You also might be able to use XMLReader to do something with it…
Wait a minute, SQL Server has XML parsing functions built in!
Maybe you see where this is going.
WARNING – this is a terrible idea
Parsing HTML with T-SQL is not a great idea. It’s dirty, it’s prone to breaking, and it will make your server’s CPUs cry that they aren’t being used for some nobler cause. If you can parse your HTML somewhere outside of SQL Server, then DO IT THERE.
With that said, if you absolutely need to parse HTML on SQL Server, the best solution is probably to write a CLR.
However, if you are stuck in a bind and plain old T-SQL is the only option available to you, then you might be able to use SQL Server’s XML datatype and functions to get this done. I’ve been there before and can sympathize.
I checked the table column I was inserting into as well and confirmed it was defined as nvarchar(max), so no problem there.
The last thing I checked was the query I was using:
SELECT JSON_VALUE(@json, '$.FiveThousandAs')
If I run that on it’s own, I reproduce the NULL I was seeing inserted into my table:
JSON_VALUE is limiting
After a little bit more research, I discovered that the return type for JSON_VALUE is limited to 4000 characters. Since JSON_VALUE is in lax mode by default, if the output has more than 4000 characters, it fails silently.
To force an error in future code I could use SELECT JSON_VALUE(@json, ‘strict $.FiveThousandAs’) so at least I would be notified immediately of an problem with my query/data (via failure).
Although strict mode will notify me of issues sooner, it still doesn’t help me extract all of the data from my JSON property.
(Side note: I couldn’t define my nvarchar(max) column as NOT NULL because for some rows the value could be NULL, but in the future I might consider adding additional database validation with a check constraint).
The solution to reading the entire 5000 character value from my JSON property is to use OPENJSON:
WITH (FiveThousandAs nvarchar(max) '$.FiveThousandAs')
My insert query needed to be slightly refactored, but now I’m able to return any length value (as long as it’s under 2gb).
In hindsight, I should have used OPENJSON() from the start: not only is it capable of parsing the full length values from JSON strings, but it performs significantly faster than any of the other SQL Server JSON functions.
As a best practice, I think I’m going to use OPENJSON by default for any JSON queries to avoid problems like this in the future.