Parsing HTML in SQL Server

Published on: 2019-02-12

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.

So anyway, here goes nothing:

Using XML to parse HTML

Let’s say we have the following fragment of HTML (copied from a bootstrap example template):

DECLARE @html xml = ' 
    <div class="container"> 
        <div class="card-deck mb-3 text-center"> 
            <div class="card-body"> 
                <h1 class="card-title pricing-card-title">$15 <small class="text-muted">/ mo</small></h1> 
                <ul class="list-unstyled mt-3 mb-4"> 
                    <li>20 users included</li> 
                    <li>10 GB of storage</li> 
                    <li>Priority email support</li> 
                    <li>Help center access</li> 
                </ul> 
                <button type="button" class="btn btn-lg btn-block btn-primary">Get started</button> 
            </div> 
        </div> 
    </div> 
'; 

If we wanted to say extract all of the text from this HTML (to allow text mining without all of the tags getting in the way) we could easily do this using the XML nodes() and value() methods:

-- Get all text values from elements 
SELECT 
    T.C.value('.','varchar(max)')  AS AllText
FROM 
    @html.nodes('/') T(C);

If we want to only extract the items from the list elements, we can write some XQuery to select only those elements:

-- Get a fragment of HTML 
SELECT 
    T.C.value('.','varchar(100)') AS ListValues 
FROM 
    @html.nodes('//*[local-name()=("li")]') T(C); 

Finally, we can also do things like select HTML fragments based on an attribute to parse further in subsequent steps. If I want to select the div with a class of “card-body”, I can write:

-- Get the text from within certain elements 
SELECT 
    T.C.query('.') AS CardBody 
FROM 
    @html.nodes('//div[@class="card-body"]') T(C); 

Yuck

To reiterate – you don’t want to do any of the above unless you have no other choice.

The XML parsing functions will not parse all HTML, so you may need to do some pre-processing on your HTML data first (removing invalid HTML, closing tags, etc…).

It works beautifully in the above example but your results may very. Good luck!

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Why make?

This post is a response to this month’s T-SQL Tuesday #111 prompt by Andy Leonard.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

In this month’s topic Andy asks why do we do what we do?


Two years ago, I was bored. 

I’d come home from work, spend my free time watching Netflix and surfing the internet, occasionally tinker with some random side projects, and eventually going to bed. Rinse and repeat, day in and day out.

I felt unfulfilled.  While I value free time and relaxation, I had an overabundance of it.  I felt like I should be doing something more productive with at least some of that time.  I wanted to work on my “professional development” somehow, but it was extremely difficult to get motivated to work on boring career stuff.

I decided what I needed was a long-term project that would allow me to have fun and be creative, while also having some positive personal and professional development benefits; what I was looking for was the ULTIMATE side project.

After spending some time thinking about different ideas, I decided to make videos about SQL Server.  Not only would I enjoy learning more about how SQL Server works (fun), but I could get practice writing and speaking (career) as well as get to incorporate my other hobby of film making into the mix (creative).

At first it felt forced; while I enjoyed learning new things about SQL Server, it was not easy thinking of topics.  Writing and editing was strenuous, but coming up with jokes and visual ways to convey ideas was fun.  Filming (and lighting and audio recording) was hard, but editing has always been pure pleasure for me.

So while at times coming up with a weekly bit of content was challenging, I kept at it because not only was it good for me, but I incorporated enough fun and creative elements into the process to look forward to it and keep going with it.

Fast forwarding to today, the process still isn’t perfect but things have gotten better: I have enough ideas to probably last me a few years (and generating more all the time), writing is still tough but I’ve seen noticeable progress so I’m motivated to keep at it, I still don’t like being in front of a camera but I have a dramatically easier time speaking about technical topics so the practice has paid off there, and while every episode isn’t as creative as I’d like, I have a lot of fun being weird and coming up with new ideas for weekly videos.

Not only that, I now have new motivating factors that I didn’t have from day one.  I’ve made friends with a lot of people in the SQL Server community, and they are fantastic and supportive.  Many of them even want to collaborate and make fun videos which is something I always look forward to.  The audience that consumes the content is wonderful as well; every time I receive a thank you email or comment, I am filled with joy.  And obviously all of the skills I have learned – technical, presenting, and networking – have helped immensely in my day-to-day.

In conclusion, the reasons that caused me to start creating SQL Server videos still apply, however over time that list of motivators has grown and helps me continue to remain excited about what I do, even when the challenges feel greater some weeks than others.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Automating Database Maintenance with Jess Pomfret and dbatools

Published on: 2019-02-05

dbatools is one of the coolest community projects I’ve seen – it is amazing how many commands are available to help make managing your SQL Server instances a breeze.

This week I had the opportunity to learn how to use dbatools to automate backups, change recovery models, and discover additional dbatools commands from dbatools contributor Jess Pomfret.

The video above goes over the basics, but be sure to check out Jess’s companion blog post to learn more about these commands.

And once you start using dbatools and have ideas for adding more functionality, check out a previous video I did with Drew Furgiuele to learn about contributing to the community project yourself.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!