XPath Problem
-
I'm trying to learn how to use XPath queries and it's just not working for me. For example, I'm trying to work out a method for querying XML from execution plans. The basic query to get the estimated values is well documented and simple. Now I want to step it up a level and get the actual values. Here's a sample of the XML (brackets substituted):
[RelOp AvgRowSize="9" EstimateCPU="0.0014337" EstimateIO="0.0138859" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1433.7" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="2.57266"][OutputList /]
[RunTimeInformation]
[RunTimeCountersPerThread Thread="0" ActualRows="1914" ActualEndOfScans="1" ActualExecutions="1" /]
[/RunTimeInformation]
and here's the query:
SELECT RelOp.op.value('@NodeId','int') AS NodeId,RelOp.op.value('@PhysicalOp','NVARCHAR(75)') AS Operation
,RelOp.op.value('./RunTimeInformation/RunTimeCountersPerThread[1]/@ActualRows','int')
FROM @xml.nodes('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//RelOp') AS RelOp(op)
It's that third column where I try to step down inside the elements to get other properties that I'm hanging on. I keep getting the errror:
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'I've tried these variations as well:
,RelOp.op.value('./RunTimeInformation/RunTimeCountersPerThread/@ActualRows','int'),RelOp.op.value('//RunTimeInformation/RunTimeCountersPerThread/@ActualRows','int')
,RelOp.op.value('QueryPlan/RelOp/RunTimeInformation/RunTimeCountersPerThread/@ActualRows','int')
Clearly I'm missing the boat on part of this whole XPath thing. Can anyone offer a bit of advice?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
The problem is that your XML could have multiple RunTimeInformation elements such as this
[RelOp AvgRowSize="9" EstimateCPU="0.0014337" EstimateIO="0.0138859"][RunTimeInformation]
[RunTimeCountersPerThread Thread="0" ActualRows="1914" ActualEndOfScans="1" ActualExecutions="1" /]
[/RunTimeInformation]
[RunTimeInformation]
[RunTimeCountersPerThread Thread="9999" ActualRows="9999" ActualEndOfScans="1" ActualExecutions="1" /]
[/RunTimeInformation]
[/RelOp]
XQuery.value must resolve to a single element so you need to do this
,RelOp.op.value('./RunTimeInformation[1]/RunTimeCountersPerThread[1]/@ActualRows','int')or this
,RelOp.op.value('(./RunTimeInformation/RunTimeCountersPerThread)[1]/@ActualRows','int')____________________________________________________
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
-
OK. I think I get why you're using the [1] twice in the first example, but what do the parenthesis do in the second?
Oh, and thanks for the help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
OK. It ran, but it's returning null for all the values and that's wrong. A little more help please?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Either remove the "declare default element namespace" clause from the query or add the namespace to the XML
[RelOp xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" AvgRowSize="9" ...____________________________________________________
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 -
Sorry, I only posted part of the XML. It's the XML from an execution plan. Here's a section of the entire XML file (brackets replaced):
[ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.3042.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"][BatchSequence]
[Batch]
[Statements]
[StmtSimple StatementCompId="3" StatementEstRows="1433.7" StatementId="1944" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="2.57266" StatementText="INSERT INTO [#fmg_broker] ( [NAME], [Rating], [PCCode], [CC] ) SELECT fmg_broker.fmg_brokername Name, fmg_broker.fmg_brokerratingname Rating, fmg_broker.fmg_producercode PCode, bu.name CC FROM FilteredFMG_Broker AS fmg_broker INNER JOIN dbo.FilteredBusinessUnit bu ON bu.businessunitid = fmg_broker.owningbusinessunit ORDER BY cc " StatementType="INSERT"]
[StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /]
[QueryPlan DegreeOfParallelism="0" MemoryGrant="413" CachedPlanSize="296" CompileTime="143" CompileCPU="143" CompileMemory="4544"]
[RelOp AvgRowSize="9" EstimateCPU="0.0014337" EstimateIO="0.0138859" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1433.7" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="2.57266"]
[OutputList /]
[RunTimeInformation]
[RunTimeCountersPerThread Thread="0" ActualRows="1914" ActualEndOfScans="1" ActualExecutions="1" /]
[/RunTimeInformation]
If I do what you suggest with this structure, it returned zero rows instead of NULL values for the @ActualRows.
XPath is not exactly friendly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
> XPath is not exactly friendly.
Yep!
,RelOp.op.value('(./*:RunTimeInformation/*:RunTimeCountersPerThread)[1]/@ActualRows','int')____________________________________________________
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 -
F'ing lovely. Worked perfect. Thank you.
Now what the heck did you do?:w00t:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply