I have a situation where I have to decide between receiving XML as an input parameter to the stored procedure or a list of comma separated values and parse them using a multi-table valued function.
Logically speaking, I would prefer XML over udfs coz' udfs can cause a lot of performance issues. But I see that XML in the backend also uses table valued functions to parse the xml. I wanted to know if XML is indeed better than Multi table UDF and if so, what makes it better?
CREATE FUNCTION [dbo].[tmp_array_commaValues] ( @string varchar(4000))
RETURNS @final TABLE(Value varchar(100))
AS
begin
WITH tmp_cte(start, stop) AS
(
SELECT 1, CHARINDEX(',' , @string )
UNION ALL
SELECT stop + 1, CHARINDEX(',' ,@string , stop + 1)
FROM tmp_cte
WHERE stop > 0
)
insert into @final
SELECT SUBSTRING(@string , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM tmp_cte
return
end
/* query */
Select value
From [dbo].[tmp_array_commaValues]('TAG,ABC,ZYX,BAG,TRY,LAG,LEAD,NETHERLANDS,TAG1,ABC1,ZYX1,BAG1,TRY1,SUN12,NANGE1')
/********** XML *********/
Declare @xml XML = '<root><id>Tag</id><id>Lag</id>
<id>Tag1</id><id>Lag1</id>
<id>Tag2</id><id>Lag2</id>
<id>Tag3</id><id>Lag3</id>
<id>Tag4</id><id>Lag4</id>
<id>Tag5</id><id>Lag5</id>
<id>Tag6</id><id>Lag6</id>
<id>Tag7</id><id>Lag7</id>
</root>'
select t.c.value('.','varchar(100)') as string
From @xml.nodes('root/id/text()') As t(c)
Execution Plan for UDF: https://www.brentozar.com/pastetheplan/?id=SJpX2DMwi
Execution Plan for XML: https://www.brentozar.com/pastetheplan/?id=ByQv2PMvo
