How good T-SQL in generating XML for multiple rows?

Question

Hi everybody,

We have a complex extract process which first selects rows of potential updates (say, 100K rows of Ids), then makes a loop over these 100K rows and calls a stored procedure that returns XML through output parameter and this XML is inserted into another table and also sha value is calculated using a custom UDF. The procedure to generate XML is quite complex (it creates table variable with supporting data first, then uses very complex query with many FOR XML PATH inside, then uses `SET @XML .modify('

delete //[not(node()) and not(./@)]

');`

before finally returning this back as a result through output parameter.

My question is - do you think it's possible to change that procedure into a table level function (and if possible inline although I don't see how to remove empty nodes unless there is a way to run the statement above on multiple rows after returning them - if yes - how)? What is the reasonable amount of rows (in your experience) to generate such XML (in SQL 2016)? Assuming we can change that procedure into a table valued function we can call it for, say, 500 rows at once.

Thanks in advance.

Answer 1

I got it to work and tested for 10K rows (used same providerid, though). It took 24 seconds for 10K rows. It will definitely be a huge improvement.