T-SQL Filter XML Variable

Question

Hello, I was wondering which one of the following two was the better way of filtering on a column in my xml variable.

INSERT INTO [Tracking].[Team]([Name], [Description], [Begin_Dt], [End_Dt], [Update_Dt])
SELECT 
        x.[Team].value('Name', 'varchar(100)') AS [Name],
        x.[Team].value('Description', 'varchar(250)') AS [Description],
        x.[Team].value('Begin_Dt', 'datetime') AS [Begin_Dt],
        x.[Team].value('End_Dt', 'datetime') AS [End_Dt],
        getdate() as [Update_Dt]
FROM @xml.nodes('/Team') x([Team])
WHERE x.[Team].value('Team_Ref_ID', 'int') = 0


INSERT INTO [Tracking].[Team]([Name], [Description], [Begin_Dt], [End_Dt], [Update_Dt])
SELECT 
        x.[Team].value('Name', 'varchar(100)') AS [Name],
        x.[Team].value('Description', 'varchar(250)') AS [Description],
        x.[Team].value('Begin_Dt', 'datetime') AS [Begin_Dt],
        x.[Team].value('End_Dt', 'datetime') AS [End_Dt],
        getdate() as [Update_Dt]
FROM @xml.nodes('/Team') x([Team])
WHERE x.[Team].exist('Team_Ref_ID[. = 0]') = 1

Notice the WHERE clause, one uses exist, the other uses value, or is there a third method that's more effective ?

Thanks, Raul

Answer 1

For querying an @xml variable the best improvement you can do is to have it be of type with an XML schema, see Typed XML Compared to Untyped XML. This will benefit the XPath expressions in your query (the .nodes('/Team'), the various .value and the .exists operators). But there will be an upfront cost in validating the schema when the variable is assigned.

The other typical XML performance improvement is XML indexes, but unfortunately they cannot be applied to variables so the point is mute in regard to your problem.

As to your particular minutia question (whether the .value('Team_Ref_ID') is faster than .exists('Team_Ref_ID[. = 0]')) I think the former is faster, but I have no evidence and I may well be wrong.

Answer 2

Actually what i found to be better was this

INSERT INTO [Tracking].[Team]([Name], [Description], [Begin_Dt], [End_Dt], [Update_Dt])
SELECT 
        x.[Team].value('Name', 'varchar(100)') AS [Name],
        x.[Team].value('Description', 'varchar(250)') AS [Description],
        x.[Team].value('Begin_Dt', 'datetime') AS [Begin_Dt],
        x.[Team].value('End_Dt', 'datetime') AS [End_Dt],
        getdate() as [Update_Dt]
FROM @xml.nodes('/Team[Team_Ref_Id = 0]') x([Team])

It filters out the nodes in the xml parser instead of having to filter them out in the SQL.