Transact SQL – XQuery – Error – “XQuery [query()]: Attribute may not appear outside of an element”

Background

Stole one of Bob Beauchemin’s Code and had to understand it.

 

  1. Author :- Bob Beauchemin
  2. Topic :- Move over developers! SQL Server XQuery is actually a DBA tool
  3. Link :- Link
  4. What does the code do
    • Looks for queries that have operators bearing a specific operator

 

Enhancement Envisioned

  1. Include the operator node in the list of projected records

Error

Textual


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


Image

 

Versions of SQL Server

  1. sql Server Version :- Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) – 13.0.4435.0 (X64)
    Apr 27 2017 17:36:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Code

Original Code

Here is the original Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Original]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Original] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Original]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Author :- Bob Beauchemin
        Topic  :- Move over developers! SQL Server XQuery is actually a DBA tool
        Link   :-	https://www.sqlskills.com/blogs/bobb/move-over-developers-sql-server-xquery-is-actually-a-dba-tool/

    */

    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            --, relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
    CROSS APPLY p.query_plan.nodes
				('
					declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
					/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
				'
				) relOp(node)
    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO



Revised Code

Here is the revised code – version 1

dbo.sp_LookForPhysicalOps.Revised01



use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised01]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01] as ')

end
go

print 'sql Server Version :- ' + @@VERSION
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 24 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element
    */

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            , relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 32 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element

    */
    CROSS APPLY p.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') relOp(node)

    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO



Compile Error


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


dbo.sp_LookForPhysicalOps.Revised02

Overview

  1. Added
  2. Modifies
    • Split into nodes
      • Original
        • CROSS APPLY p.query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable(“@op”)]’) relOp(node)
      • Revised
        • CROSS APPLY p.query_plan.nodes(‘//RelOp[@PhysicalOp = sql:variable(“@op”)]’) relOp(node)
      • Explanation
        • Please properly indicate attribute name
          • Change from /@PhysicalOp[.
          • To [@PhysicalOp

Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised02]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
                TOP 
                (
                    case
                        when  @maxNumberofRecords is  null then 10000
                        when  @maxNumberofRecords = 0 then 10000
                        else @maxNumberofRecords
                    end
                )

              [sql].[text]

            , [executionCount]
				= qs.execution_count

            , qp.query_plan

			, [nodeID] 
				= relOp.node.value
					(
						  '@NodeId'
						, 'integer'
					)

            , [node] 
				= relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp

    CROSS APPLY qp.query_plan.nodes
				(
					'//RelOp[@PhysicalOp = sql:variable("@op")]'
				) relOp([node])


    WHERE qp.query_plan.exist
		  ('
            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
          ') = 1

	order by
		qs.execution_count desc

end

GO


Invoke



declare @op sysname
declare @maxNumberofRecords int = null

set @op = 'Hash Match'

set  @maxNumberofRecords = 5

EXECUTE [dbo].[sp_LookForPhysicalOps.Revised02]
          @op =  @op
        , @maxNumberofRecords = @maxNumberofRecords



Output

 

Summary

And so this works


    WHERE qp.query_plan.exist
		  ('
            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
          ') = 1

 

but, this does not


    CROSS APPLY p.query_plan.nodes
			(
				'/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]'
			) relOp([node])

 

The period (/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable(“@op”) ) works on exist, but not on the cross apply ( /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. ).

Addendum

Addendum – 2018-04-09

The code above has a lot of deficiencies.

Kept the deficiencies has I did want to deviate too much from my young desire to address an error message.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s