SQL Server – How to Test if XML Equals Empty String

optimizationperformancequery-performancesql serversql-server-2005xml

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter.

Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a parameter.

here is the code of the stored procedure:

CREATE PROCEDURE [DENORMV2].[udpProductBulletPointSelectByTier1NoteTypeCode] (  
    @Tier1 VARCHAR(10),  
    @LanguageID INT,  
    @SeasonItemID VARCHAR(5) = NULL,
    @ListNoteTypeCode XML,  
    @CacheDuration INT OUTPUT )  
    WITH EXECUTE AS 'webUserWithRW'  
AS

        SELECT  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,  
                pbp.NoteGroup, pbp.SortOrder  
        FROM    dbo.ProductBulletPoint pbp  

        WHERE   Tier1 = @Tier1 
          AND   LanguageId = @LanguageID 
          AND   (      SeasonItemId = @SeasonItemID  
                  OR
                       @SeasonItemID is null
                )

          AND pbp.NoteTypeCode IN (

                 SELECT  NoteTypeCode=BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)')
                   FROM  @ListNoteTypeCode.nodes('/BulletPoint/NoteTypeCode') AS BulletPoint ( NoteTypeCode )

          )

SELECT  @CacheDuration = Duration  
FROM    dbo.CacheDuration  
WHERE   [Key] = 'Product'
GO

more info about this procedure here

this is an example of how it can be called:

declare @p5 int  set @p5=86400 
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output  select @p5

Question:

what is the best way to find out whether or not the parameter @ListNoteTypeCode XML is empty?

what if they call this procedure like this:

declare @p5 int  set @p5=86400 
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output  select @p5

as it was suggested here I could avoid the select altogether by testing the parameter @ListNoteTypeCode.

My main goal in this scenario is to retrieve the data in the best possible performance, since this procedure is not cached in the web-servers and is called over a million times a day.

Best Answer

You may check for NULL and nodes absence (exist method of xml type):

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

Your XPath can be more specific, if necessary:

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('/BulletPoint/NoteTypeCode/text()') = 0
Related Question