XPath query against a query plan - xmlns giving me problems

Question

Post reply

XPath query against a query plan - xmlns giving me problems

Jimmy M

Default port

Points: 1483
More actions
April 8, 2011 at 5:51 am

#100369

Hi all, I am trying to return the parameters a stored proc was compiled with by doing an XPath query against the query plan.
The problem i have is that the xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" returned in the root element ShowPlanXML is causing the query to return no results.
Please see the first SELECT vs the 2nd one below. The 1st returns no results and the 2nd one just removes the unwanted attributes in the root element and then works fine.
DECLARE @x xml, @y xml
select@x = query_plan
fromsys.dm_exec_procedure_stats ps
CROSS APPLYsys.dm_exec_query_plan(ps.plan_handle) as qp
whereobject_id = object_id('myProc')
--Original SELECT returns 0 results
SELECTt.c.value('@Column', 'varchar(128)') AS 'Parameter', t.c.value('@ParameterCompiledValue', 'varchar(128)') AS 'Value'
from@x.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference') as T(c)
--Remove the attributes in the root element and have another go.....
set @y = cast(replace(cast (@x as nvarchar(max)), ' xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.4000.0"', '') as xml)
SELECTt.c.value('@Column', 'varchar(128)') AS 'Parameter', t.c.value('@ParameterCompiledValue', 'varchar(128)') AS 'Value'
from@y.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference') as T(c)
My question is: What is the proper way of handling xmlns in the root element? I have a feeling this is basic XML stuff that i just don't understand!
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try this

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT t.c.value('@Column', 'varchar(128)') AS 'Parameter', t.c.value('@ParameterCompiledValue', 'varchar(128)') AS 'Value'

from @x.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference') as T(c)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jimmy M Default port Points: 1483 More actions · Answer 2

Thanks so much. I saw the WITH XMLNAMESPACES references in BOL but the examples seemed to imply the XML elements were themselves prefixed with the namespace declared within WITH XMLNAMESPACES. The difference is the DEFAULT keyword then i gather.

Thanks again.

xilon2000 SSC Enthusiast Points: 109 More actions · Answer 3

One more way to do the same thing is declaring default element namespase "http://schemas.microsoft.com/sqlserver/2004/07/showplan" in the XQuery:

SELECT t.c.value('@Column', 'varchar(128)') AS 'Parameter', t.c.value('@ParameterCompiledValue', 'varchar(128)') AS 'Value'

from @x.nodes

('

declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";

/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference

') as T(c)

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 4

After searching for an answer for why this yields no result, I've circled back here. Is there something that needs to be enabled in order to view the results? The sys.dm_exec_query_stats cross applied to the query plan produces results, but the xml path comes back empty.

Jamie

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Are you sure you actually have parameters? Mark's sample returns an empty result set if there aren't any. My sample didn't have any, but I could pull the output list instead.

Without seeing the specific plan XMl, it may be tough to debug.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?