Q:

Why SQL function always returning empty result set?

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

A:

XQuery is case-sensitive, as are things generally when dealing with XML.  Correct your function to have an upper-case Listing element and it will work.  I note you also pass in @ID but don't use it and have accidentally swapped around your Value1 and Value2 columns. You should also always use the text() accessor when reading untyped XML for a performance boost, as in my example below.
This worked for me:
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('(Value1/text())[1]','INT'),
        l.v.value('(Value2/text())[1]','INT')
    FROM @XML_Details.nodes('/Listings/Listing')l(v)

    RETURN
END
GO

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

My results:

Just as an aside, your function is simple enough to be an inline table-valued function rather than a multi-line one.  Inline TVFs generally perform better.  Post back if you want to know more about that.
HTH

