Why SQL function always returning empty result set?

Question

Following function I have write which accept xml and returns table as result.

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN
    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT 
        l.v.value('Value2[1]','INT'),
        l.v.value('Value1[1]','INT')
    FROM @XML_Details.nodes('/Listings/listing')l(v)
    RETURN
END

And following is the code I using to run against above function but it always returning Empty result.

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)


DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT * 
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select 
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result

Please do not make edit that way (in fact asking whole new question). Your orignal problem has been solved. If you need futher assistance consider asking new question with http://sqlfiddle.com demo that **include your real schema** and data. [More info about chameleon questions](http://meta.stackexchange.com/questions/43478/exit-strategies-for-chameleon-questions) — Lukasz Szozda, Mar 02 '16 at 12:20

score 3 · Accepted Answer · edited May 23 '17 at 11:44

First of all avoid multiline function when it is possible. Inline UDFs have better pefromance.

Second XQuery is case-sensitive and you don't have '/Listings/listing' path.

Third: you probably want to filter using @ID.

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS  TABLE
AS
RETURN(
    SELECT Value1, Value2
    FROM (SELECT  
            l.v.value('(Value2)[1]','INT') AS Value2,
            l.v.value('(Value1)[1]','INT') AS Value1
          FROM @XML_Details.nodes('/Listings/Listing')l(v)) AS sub
    WHERE Value1 = @ID
    )
GO

DECLARE @tbl_Xml_Result Table(Value1 INT,Value2 INT);
INSERT INTO @tbl_xml_Result(Value1, Value2)
values(1,2),(2,3),(3,4),(4,5),(5,6);

DECLARE @xml_Temp xml  = (SELECT * 
                          FROM @tbl_xml_Result
                          FOR XML PATH('Listing'),ROOT('Listings'));

SELECT T.Value1,
       T.Value2
FROM FunctionTest(1,@xml_Temp) T;

LiveDemo

score 2 · Answer 2 · answered Mar 02 '16 at 12:10

2

The problem in the 19th line of your function:

    FROM @XML_Details.nodes('/Listings/listing')l(v)

*listing - and you need Listing

answered Mar 02 '16 at 12:10

Alex Yu

3,412
1
25
38

Why SQL function always returning empty result set?

2 Answers2