sql server - TSQL xQuery how to I get the root/document node -

i have xml field contains data similar how .net constructs controls within forms. suppose have windows form, can add multiple controls form , show under .controls property. of controls can have controls such panels, group boxes etc. similar shown in xml below.

<form> <name>myform</name> <tabctrl>     <name>tab1</name>     <controls>         <textboxctrl>             <name>mytextbox</name>             <location>3,10</location>             <tag>34</tag>         </textboxctrl>         <label>             <name>mylabel</name>             <location>23,3</location>             <tag>19</tag>>         </label>         <panel>             <name>mypanel</name>             <controls>                 <textboxctrl>                     <name>mytextbox2</name>                     <location>36,210</location>                     <tag>34</tag>                 </textboxctrl>             </controls>         </panel>     </controls> </tabctrl> <tabctrl>     <name>tab2</name>     <controls>         ...     </controls> </tabctrl> 

there thousands of rows in db table each xml consists of 1000’s of “controls”. i’m looking way query tabctrl/name node when tabctrl contains control has tag of 34. can limit rows query using xpath

select thexml viewtable thexml.exist('//tag[.="34"]') = 1 

further, can name of control rather entire xml this:

select thexml.query('//*[tag="34"]/label/text()') 'control name'from viewtable thexml.exist('//tag[.="34"]') = 1 

how tabctrl/name ? path element contains matching tag go through 1-n levels of controls nodes using xpath statement won’t work. tabctrl ways direct child of form node

possible solutions :

one possible way text tabctrl/name containing control has tag of 34 :

select thexml.query('//tabctrl[controls/*/tag="34"]/name/text()') 'control name' 

or if need <tabctrl> starting it's descendant can climb tree using parent::element_name or ancestor::element_name or .. :

select thexml.query('//*[tag="34"]/ancestor::tabctrl/name/text()') 'control name' 

difference between axes mentioned above :

• parent::element_name : go one level specific parent element

• ancestor::element_name : go one or more level specific ancestor element

• .. : go one level parent element any name