0

Script to create table and function at the bottom of the post:

We have lately ran into problem that sometimes our stored procedures or Multi-statement table value that include sql statement parsing XML don't return values.

Usually just altering SP or Function (without any changes) fixes the problem. That's why I think it's plan cache related. I've rewritten some of these functions to ITVF's, but not all of them can be,

Any idea what can cause stored procedure to not return data, but same (standalone) select to return data correctly? Is this parametr sniffing related (ANSI settings is the same)?

We have SQL Server 2016 SP2 CU2

CREATE TABLE TreeNode
(
    id BIGINT IDENTITY 
    , metadata XML
)

GO

INSERT INTO dbo.TreeNode (metadata)
    VALUES (CAST('<TreeNodeMetadata>
  <Section Code="SectionCode1">
    <Value Code="SectionCode1_ValueCode1" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode2" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode3" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode4" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode5" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode6" Type="System.Boolean">True</Value>
    <Value Code="SectionCode1_ValueCode7" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode2">
    <Value Code="SectionCode2_ValueCode1" Type="System.Boolean">True</Value>
    <Value Code="SectionCode2_ValueCode2" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode3" Desc="Section Desc 3">
    <Value Code="SectionCode3_ValueCode1" Type="System.Boolean">True</Value>
    <Value Code="SectionCode3_ValueCode2" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode4" Desc="Section Desc 4">
    <Value Code="SectionCode4_ValueCode1" Type="System.Boolean" Desc="">True</Value>
    <Value Code="SectionCode4_ValueCode2" Type="System.Boolean" Desc="">True</Value>
    <Value Code="SectionCode4_ValueCode3" Type="System.Boolean" Desc="">True</Value>
    <Value Code="SectionCode4_ValueCode4" Type="System.Boolean" Desc="">True</Value>
    <Value Code="SectionCode4_ValueCode5" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode5" Desc="Section Desc 5">
    <Value Code="SectionCode5_ValueCode1" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode6" Desc="Section Desc 6">
    <Value Code="SectionCode6_ValueCode1" Type="System.Boolean">True</Value>
  </Section>
  <Section Code="SectionCode7" Desc="">
    <Value Code="SectionCode7_ValueCode1" Type="System.Boolean">True</Value>
    <Value Code="SectionCode7_ValueCode2" Type="System.Boolean">True</Value>
    <Value Code="SectionCode7_ValueCode3" Type="System.Boolean">True</Value>
  </Section>
</TreeNodeMetadata>' AS XML));



CREATE FUNCTION [dbo].TestXmlParsin
(
    @treeNodeId INT                     ---<ID TreeNodu>---
)
RETURNS @returnTable TABLE
(
    [SectionCode] NVARCHAR(50)
    ,[SectionName] NVARCHAR(100)
    ,[SectionDesc] NVARCHAR(500)
    ,[Code] NVARCHAR(50)
    ,[Name] NVARCHAR(100)
    ,[Type] NVARCHAR(100)
    ,[Desc] NVARCHAR(500)
    ,[Value] NVARCHAR(4000)
)
AS
BEGIN
    INSERT INTO @returnTable
    (
        [SectionCode]
        ,[SectionName]
        ,[SectionDesc]
        ,[Code]
        ,[Name]
        ,[Type]
        ,[Desc]
        ,[Value]
    )
        SELECT
            r.c.value('@Code', 'NVARCHAR(50)')              AS [SectionCode]
            ,r.c.value('@Name', 'NVARCHAR(100)')            AS [SectionName]
            ,r.c.value('@Desc', 'NVARCHAR(500)')            AS [SectionDesc]
            ,col.v.value('@Code', 'NVARCHAR(50)')           AS [Code]
            ,col.v.value('@Name', 'NVARCHAR(100)')          AS [Name]
            ,col.v.value('@Type', 'NVARCHAR(100)')          AS [Type]
            ,col.v.value('@Desc', 'NVARCHAR(500)')          AS [Desc]
            ,col.v.value('.[1]', 'NVARCHAR(MAX)')           AS [Value]
        FROM
            dbo.TreeNode tn
            CROSS APPLY tn.[Metadata].nodes('//TreeNodeMetadata/Section') r(c)
            CROSS APPLY c.nodes('*') col(v)
        WHERE
            tn.[Id] = @treeNodeId
    RETURN
END

GO

SELECT * from TestXmlParsin(1)


SELECT
    r.c.value('@Code', 'NVARCHAR(50)')              AS [SectionCode]
    ,r.c.value('@Name', 'NVARCHAR(100)')            AS [SectionName]
    ,r.c.value('@Desc', 'NVARCHAR(500)')            AS [SectionDesc]
    ,col.v.value('@Code', 'NVARCHAR(50)')           AS [Code]
    ,col.v.value('@Name', 'NVARCHAR(100)')          AS [Name]
    ,col.v.value('@Type', 'NVARCHAR(100)')          AS [Type]
    ,col.v.value('@Desc', 'NVARCHAR(500)')          AS [Desc]
    ,col.v.value('.[1]', 'NVARCHAR(MAX)')           AS [Value]
FROM
    dbo.TreeNode tn
    CROSS APPLY tn.[Metadata].nodes('//TreeNodeMetadata/Section') r(c)
    CROSS APPLY c.nodes('*') col(v)
WHERE tn.id = 1

Problem is the code works. But once the code is wrapped in SP or MTVF, it sometimes return a correct row count, but will NULLs instead of values. That's why I think it's related to plan cache.

I wasn't able to replicate it. Basically I'm looking for a known issue with XML and perhaps nodes function or cached plans (because that's the common denominator).

  • 2
    If your example doesn't replicate it, it's not enough, sorry. When you're looking for issues like the ones you've mentioned (product bugs), open a support ticket with Microsoft. It's $500, but if you find a flaw, they'll refund it. – Erik Darling Jan 16 at 13:57
  • Once the problem starts, does it happen consistently until you alter the proc or TVF? If "Usually just altering SP or Function (without any changes) fixes the problem.", then how about trying sp_recompile on that proc / TVF instead of altering? Or, drop the proc cache for that DB only. That will at least determine if this is plan cache related or not. – Solomon Rutzky Jan 17 at 7:18
  • Yes, we had a job run this proc every minute and it failed (by trying to parse non-returned data) consistently, until we've altered. I haven't tried sp_recompile, but I think it would have the same effect - I've fixed two of the function by rewritting them to ITVF which don't save plan in a cache - same thing would probably by solved by providing OPTION (RECOMPILE) on the XML parsing statement. – Zikato Jan 17 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.