CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2012
Kaishu
Kaishu

Reputation: 377

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

Upvotes: 2

Views: 301

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

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

Upvotes: 3

Alex Yu
Alex Yu

Reputation: 3547

The problem in the 19th line of your function:

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

*listing - and you need Listing

Upvotes: 2

Related Questions