Calling User-Defined Function in select statement returning xml data

Question

I created a user-defined function in SQL Server 2012 that returns XML. I would like to call the function in a SELECT statement. Is this possible? When I try doing it, I get the error:

The FOR XML clause is not allowed in a ASSIGNMENT statement.

I want the SELECT statement to return a set of these named methods that have dependencies of other named methods within their logic. In the main CTE, I get the latest versions of methods that have dependencies. The UDF goes thru the logic of each method and returns any methods called within it. So, I want to call the UDF in the SELECT statement and return XML of the dependent method names.

The function works and returns XML data. This is the function:

ALTER FUNCTION [dbo].[GetCalledMLMs] 
(
    -- Add the parameters for the function here
    @MLM_Txt nvarchar(MAX)
)
RETURNS XML
AS
BEGIN
    -- Declare the return variable here
    DECLARE @CalledMLMs XML
    Declare @MLMTbl table (pos int, endpos int, CalledMLM nvarchar(200))
    --Logic to get the data...

    Select @CalledMLMs = CalledMLM from @MLMTbl FOR XML PATH

    -- Return the result of the function
    RETURN @CalledMLMs

END

This is the CTE that calls the UDF.

;with cte as
(
select distinct Name, max(ID) as LatestVersion
from MLM_T 
where Logic like '%:= MLM %' and Logic not like '%standard_libs := mlm%'
group by Name
)
select MLM2.Name, LatestVersion, 
dbo.GetCalledMLMs(MLM2.Logic) as CalledMLMs
from cte join MLM_T MLM2 on cte.Name = MLM2.Name 
    and cte.LatestVersion = MLM2.ID
    and MLM2.Active = 1 and MLM2.Status in (3, 4)

When running this query I get the error that XML is not allowed to be used in assignment statement. Is there any way to call a function in the SELECT statment that returns an XML data type?

Gottfried Lesigang · Accepted Answer · 2016-05-04 19:48:13Z

2

If you want to set a variable to a value you have to use SET and a scalar value on the right side.

The syntax SELECT @SomeVariable=SomeColumn FROM SomeTable is not possible with FOR XML (and rather dangerous anyway...), because the XML is not a column of the SELECT but something after the process of selecting.

Your problem is situated here:

Select @CalledMLMs = CalledMLM from @MLMTbl FOR XML PATH

Try to change this to

SET @CalledMLMs = (SELECT CalledMLM FROM @MLMTbl FRO XML PATH);

answered May 4, 2016 at 19:48

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gloria Santin · Accepted Answer · 2016-05-04 15:41:50Z

1

I solved the problem by changing the function to return a table, not XML. So it looks like this:

FUNCTION [dbo].[GetCalledMLMsTbl] 
(
    -- Add the parameters for the function here
    @MLM_Txt nvarchar(MAX)
)
--RETURNS XML
RETURNS @MLMTbl TABLE
(
    pos int,
    endpos int,
    CalledMLM nvarchar(200)
)
AS
BEGIN
  --logic here
  insert into @MLMTbl (pos, endpos, CalledMLM) Values (@startpos, @endpos, @MLM_name)
RETURN
END

Then I called the function in the 'from' clause in the select

;with cte as
(
select distinct Name, max(ID) as LatestVersion
from CV3MLM 
where Logic like '%:= MLM %' and Logic not like '%standard_libs := mlm%'
    --and Name not like '%V61_CCC' 
group by Name
)
select MLM2.Name, LatestVersion, C.CalledMLM 
from cte join MLM_tbl MLM2 on cte.Name = MLM2.Name and cte.LatestVersion = MLM2.ID
    and MLM2.Active = 1 and MLM2.Status in (3, 4)
    cross apply dbo.GetCalledMLMsTbl(MLM2.Logic) C
order by MLM2.Name, LatestVersion

answered May 4, 2016 at 15:41

Gloria Santin

2,1167 gold badges63 silver badges138 bronze badges

1 Comment

Gloria Santin Over a year ago

Sorry, I did not realize that I could only accept check 1 answer. I will select yours since that solved the original question. Thanks for the note.

Collectives™ on Stack Overflow

Calling User-Defined Function in select statement returning xml data

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related