XPath query against a query plan - xmlns giving me problems

  • Jimmy M

    Default port

    Points: 1481

    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

    from sys.dm_exec_procedure_stats ps

    CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) as qp

    where object_id = object_id('myProc')

    --Original SELECT returns 0 results

    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)

    --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)

    SELECT t.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

  • Mark Cowne

    One Orange Chip

    Points: 26692

    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: 1481

    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

    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: 8151

    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: 124185

    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?

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

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