Identifying XML Node Position in SQL Server 2008
-
I am having a problem identifying the node position that contains a particular element in a piece of XML.
In the attached example I would like to extract the position of each node within the statements section containing the MissingIndexes element. So there are four nodes within the statements section and nodes 2 and 4 contain the MissingIndexes element. I would like to return the values 2 and 4.
Is this possible to do with SQL Server? I have a table of execution plans and I would like to extract this information from it. I've looked at the position function but that only works with predicates so I don't think I can use it.
Forgive me if I have used incorrect terminology anywhere; my XML knowledge is quite poor.
Any help appreciated.
-
To the best of my knowledge have to use a construct of this sort to find the position:
declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<bob id="2"/>
<bob id="-8"/>
<bob id="16"/>
<bob id="-32"/>
<bob id="64"/>
<bob id="-128"/>
</bill>
</r>'
select x.value('(@id)[1]','int'),
ROW_NUMBER() over (partition by b order by x ) --returns your position
from @xml.nodes('/r/bill') a(b)
cross apply b.nodes('bob') c(x)
----------------------------------------------------------------------------------
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? -
I have tried this approach but it only works for simple examples where you already know the order of the elements and they have the same names at the same level. In the example I attached sometimes the subelement of Statements is StmtSimple and sometimes it is StmtCond. Also, the MissingIndexes element is at different levels. Do you know how to adapt your script to handle this specific example?
-
Assuming you don't want to reset the count, and just want to count as you find them in the file, you can use the // trick. As in:
declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<bob id="2"/>
<bob id="-8"/>
<jim><bob id="16"/></jim>
<bob id="-32"/>
<bob id="64"/>
<bob id="-128"/>
</bill>
</r>'
select x.value('(@id)[1]','int'),
ROW_NUMBER() over ( order by x ) --returns your position
from @xml.nodes('//bob') a(x) --// is XQUERY for "find anywhere in the structure"
----------------------------------------------------------------------------------
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? -
As to filtering out specific nodes with multiple names, try:
declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<boris id="2"/>
<bob id="-8"/>
<jim><bob id="16"/></jim>
<bob id="-32"/>
<bob id="64"/>
<bob id="-128"/>
</bill>
</r>'
select x.value('(@id)[1]','int'),
ROW_NUMBER() over ( order by x ) --returns your position
from @xml.nodes('//.[local-name()="bob" or local-name()="boris"]') a(x) --// is XQUERY for "find anywhere in the structure"
----------------------------------------------------------------------------------
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? -
One last one - this might help you figure out how to detect those children nodes:
declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<boris id="2"/>
<bob id="-8"/>
<jim><bob id="16"/></jim>
<bob id="-32"/>
<bob id="64"><fun>64</fun></bob>
<bob id="-128"/>
</bill>
</r>'
select x.value('(@id)[1]','int'), x.query('for $g in .[fun="64"] return string("true")').value('(text())[1]','varchar(100)'),
ROW_NUMBER() over ( order by x ) --returns your position
from @xml.nodes('//.[(local-name()="bob" or local-name()="boris")]') a(x) --// is XQUERY for "find anywhere in the structure"
Just giving you ideas how to set the various pieces up up. You've shown a small notional version of what you're up against, but without specifics I dont' know that I can give you much more than samples like the above.
----------------------------------------------------------------------------------
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? -
Here's another way
select x.value('(@id)[1]','int'),
x.value('for $a in . return 1+count($a/../*[. << $a])','int')
from @xml.nodes('/r/bill/bob') c(x)
____________________________________________________
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
-
Great, thanks for all the replies. I'll try these out.
Which sites do you use as a reference? I looked at w3schools but they just give really simple examples. Are there any more advanced sites that use examples of this complexity?
-
JWOL (8/3/2012)
Great, thanks for all the replies. I'll try these out.Which sites do you use as a reference? I looked at w3schools but they just give really simple examples. Are there any more advanced sites that use examples of this complexity?
I do use the W3* sites to help me, but I also end up relying on BOL a lot, mostly to keep me sane as to which parts of XQUERY/XPATH are implemented and supported (not everything is, so beware).
----------------------------------------------------------------------------------
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? -
declare @xml xml
set @xml='<batch>
<statements>
<StmtSimple id="1"/>
<QueryPlan id="1"/>
<StmtCond id="2"/>
<QueryPlan id="1"/>
<StmtSimple id="8"/>
<StmtSimple id="16"/>
<StmtSimple id="32"/>
<StmtSimple id="64"/>
<StmtSimple id="128"/>
</statements>
</batch>
'
select
x.value('for $a in (//statements/*)[1] return 1+count($a/../*[. << $a])','int')
from @xml.nodes('/batch/statements') c(x)
I've managed to get a count of the Statements element, which is 1, but how to get a count of 7 for the subelement still eludes me. I can get 9 which includes all of the subelements or 1 with the code above. I know I get one because I've reduced it to a singleton set with [1] but without that I get an error about it having to be singleton or empty. Where am I going wrong?
-
And how would I check for the existence of the element fun in this example?
Matt Miller (#4) (8/2/2012)
One last one - this might help you figure out how to detect those children nodes:declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<boris id="2"/>
<bob id="-8"/>
<jim><bob id="16"/></jim>
<bob id="-32"/>
<bob id="64"><fun>64</fun></bob>
<bob id="-128"/>
</bill>
</r>'
select x.value('(@id)[1]','int'), x.query('for $g in .[fun="64"] return string("true")').value('(text())[1]','varchar(100)'),
ROW_NUMBER() over ( order by x ) --returns your position
from @xml.nodes('//.[(local-name()="bob" or local-name()="boris")]') a(x) --// is XQUERY for "find anywhere in the structure"
Just giving you ideas how to set the various pieces up up. You've shown a small notional version of what you're up against, but without specifics I dont' know that I can give you much more than samples like the above.
-
Try this:
declare @xml xml
set @xml='<r><bill>
<bob id="1"/>
<bob id="2"/>
<bob id="8"/>
<bob id="16"/>
<bob id="32"/>
<bob id="64"/>
<bob id="128"/>
</bill>
<bill>
<bob id="-1"/>
<boris id="2"/>
<bob id="-8"/>
<jim><bob id="16"/></jim>
<bob id="-32"/>
<bob id="64"><fun>64</fun></bob>
<bob id="-128"/>
</bill>
</r>'
;with cte as (
select x.value('(@id)[1]','int') rID, x.query('for $g in .[fun="64"] return string("true")').value('(text())[1]','varchar(100)') funcheck,
ROW_NUMBER() over ( order by x )Rowpos --returns your position
from
@xml.nodes('//.[(local-name()="bob" or local-name()="boris")]') t(x)
)
select * from CTE where funcheck='true'
The CTE is there to calculate the position (based on counting the bob's and boris'es, then you filter out the ones you don't want.
----------------------------------------------------------------------------------
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? -
JWOL (8/3/2012)
declare @xml xml
set @xml='<batch>
<statements>
<StmtSimple id="1"/>
<QueryPlan id="1"/>
<StmtCond id="2"/>
<QueryPlan id="1"/>
<StmtSimple id="8"/>
<StmtSimple id="16"/>
<StmtSimple id="32"/>
<StmtSimple id="64"/>
<StmtSimple id="128"/>
</statements>
</batch>
'
select
x.value('for $a in (//statements/*)[1] return 1+count($a/../*[. << $a])','int')
from @xml.nodes('/batch/statements') c(x)
I've managed to get a count of the Statements element, which is 1, but how to get a count of 7 for the subelement still eludes me. I can get 9 which includes all of the subelements or 1 with the code above. I know I get one because I've reduced it to a singleton set with [1] but without that I get an error about it having to be singleton or empty. Where am I going wrong?
Pretty sure this is how you fix this one. Mark has more XQUERY-fu than I :w00t:
declare @xml xml
set @xml='<batch>
<statements>
<StmtSimple id="1"/>
<QueryPlan id="1"/>
<StmtCond id="2"/>
<QueryPlan id="1"/>
<StmtSimple id="8"/>
<StmtSimple id="16"/>
<StmtSimple id="32"/>
<StmtSimple id="64"/>
<StmtSimple id="128"/>
</statements>
</batch>
'
select
x.value('for $a in (//statements/*)[1] return 1+count($a/../*[. << $a])','int'),
x.value('for $a in . return 1+count($a/../*[.[(local-name()="StmtSimple" or local-name()="StmtCond")] << $a])','int') ll
--from @xml.nodes('/batch/statements') c(x)
from @xml.nodes('//.[(local-name()="StmtSimple" or local-name()="StmtCond")]')
a(x)
----------------------------------------------------------------------------------
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? -
JWOL (8/3/2012)
declare @xml xml
set @xml='<batch>
<statements>
<StmtSimple id="1"/>
<QueryPlan id="1"/>
<StmtCond id="2"/>
<QueryPlan id="1"/>
<StmtSimple id="8"/>
<StmtSimple id="16"/>
<StmtSimple id="32"/>
<StmtSimple id="64"/>
<StmtSimple id="128"/>
</statements>
</batch>
'
select
x.value('for $a in (//statements/*)[1] return 1+count($a/../*[. << $a])','int')
from @xml.nodes('/batch/statements') c(x)
I've managed to get a count of the Statements element, which is 1, but how to get a count of 7 for the subelement still eludes me. I can get 9 which includes all of the subelements or 1 with the code above. I know I get one because I've reduced it to a singleton set with [1] but without that I get an error about it having to be singleton or empty. Where am I going wrong?
Not quite sure what you're asking, but see if this helps
selectx.value('for $a in (//statements/*)[1] return 1+count($a/../*[. << $a])','int'),
x.value('count(//*[local-name()=("StmtSimple","StmtCond")])','int'),
x.value('count(.//StmtSimple)','int'),
x.value('count(.//StmtCond)','int'),
x.value('count(.//*)','int')
from @xml.nodes('/batch/statements') c(x)
____________________________________________________
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 -
declare @xml xml
set @xml='<batch>
<statements>
<StmtSimple id="1"/>
<QueryPlan id="1"/>
<StmtCond id="2"/>
<QueryPlan id="1"/>
<StmtSimple id="4"/>
<StmtSimple id="8"/>
<MissingIndex/>
<StmtSimple id="16"/>
<StmtSimple id="32"/>
<QueryPlan id="2"/>
<StmtSimple id="33"/>
<StmtSimple id="34"/>
<StmtSimple id="64"/>
<StmtSimple id="128"/>
</statements>
</batch>
';
I don't get any rows returned. I would like my query to return the value 3. What do I need to change please?
with cte as (
select x.query('for $g in .[(local-name()="MissingIndex")] return string("true")').value('(text())[1]','varchar(100)') chk,
ROW_NUMBER() over ( order by x )Rowpos --returns your position
from
@xml.nodes('//.[(local-name()="StmtSimple" or local-name()="StmtCond")]') t(x)
)
select * from CTE where chk='true'
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply