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 sqlfiddle.com demo that include your real schema and data. More info about chameleon questions — Lukasz Szozda
– Lukasz Szozda, Commented Mar 2, 2016 at 12:20

Community · Accepted Answer · 2017-05-23 11:44:16Z

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

Alex Yu · Accepted Answer · 2016-03-02 12:10:45Z

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 2, 2016 at 12:10

Alex Yu

3,5771 gold badge27 silver badges42 bronze badges

Collectives™ on Stack Overflow

Why SQL function always returning empty result set?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related