As we know, there are multiple ways in which we could parse an XML in SQL Server, I wanted to use the one that performs fastest.
I looked around to see if I could find some sort of performance numbers for each of the method. After a bit of googling, I found out a few places where there were bits and pieces of information which compared the different approaches. Taking inspiration from this thread, I wrote a simple test which prints out the performance stats for parsing our XML using three different approaches.
The test SQL itself has nothing specific to our application,
- It basically generates a very big XML so that we can have a decent comparison between three approaches.
- It then parses this XML using different techniques
- It uses following three approaches to parse the XML
- OPENXML
- The nodes() with text()
- The nodes() without text()
- It then spits out the performance stats for each of the approach.
Here is the Sample test SQL file.
Sample Output
As you can see:
- nodes() without text() was the slowest of all
- Then came OPENXML
- nodes() with text() was significantly faster than even the OPENXML.
Obviously, we decided to go with nodes() with text() approach. Use the sample test SQL and find for yourself which one performs best for your requirement. That's all folks!
