Friday, 2 January 2015

Pros and cons of passing parameters as XML in a Stored Procedures



Scenario: My new client's current environment uses XML as a parameter to SQL Stored Procedure. They are adding the actual parameters in a XML and passing it through and expecting resultant table in XML as well.


Issues(rather Concerns):



1. Is there any performance up-gradation if we go for XML rather then multiple parameters?



2. In XML, we need to use OPENXML./XML.nodes techniques to store them back to variables and then use them. Is there any better way (apart from using Temporry table)? 3. The expected resultant of a SP ("SELECT * FROM tbl") is XML and the SQL used for this is:



FOR XML PATH('Student'), TYPE, ROOT('Students')


Any better method of doing this?


Thanks


No comments:

Post a Comment