Using XML as a SQL Server parameter has been popularized for quite some time because it is a very flexible method of passing data. It allows you to pass scalar properties. It can also pass in one or more sets of data along for the ride. It is completely flexible. JSON works pretty much the same. There are some things you need to know when passing XML that may change your mind when you think about using it.
When you wish to parse the XML document from an input parameter or variable, and convert it into SQL objects, this is most often done by using XMLSQL.dll. Using XMLSQL.dll you can parse the document, and convert the properties of interest into SQL variables or objects by using XPath queries. This allows you to unpack many different scalar values, or a table, or even multiple tables. That is a very flexible tool.
The problem with XMLSQL.dll is that it is instantiated each time you use it in a query. You register and open the XML document in the SQL Server process. If you don’t release your handle to the instance of the DLL for any reason, it remains in memory as a memory leak. This could be because the XML was not well formed, and you query blows up when trying to read it. It could be for any reason…Computers are not perfect.
So, if you do parse your XML, be sure to encapsulate your code in a TRY/CATCH. Then, no matter what, be sure to release the handle to the DLL so you release the memory. In the meantime, consider using table parameter values and scalar parameters for your SQL calls using queries or stored procedures. These tools do not have the same issue as instantiating an external DLL.
Cheers,
Ben