CODEWITHSUNDEEP
sql-serverxmlxpathxquery
MtnManChris
MtnManChris

Reputation: 536

TSQL xQuery how to I get the root/document node

I have an XML Field that contains data similar to how .Net constructs controls within forms. Suppose you have a windows Form, you can add multiple controls to the form and they show up under the .Controls property. Some of the controls themselves can also have controls such as panels, group boxes etc. Similar to what is shown in the 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 are thousands of rows in a DB table each with xml that itself consists of up sometimes 1000’s of “controls”. I’m looking for a way to query for TabCtrl/Name node when that TabCtrl contains a control that has the Tag of 34. I can limit the rows by this query using xPath

Select theXML from ViewTable where theXML.exist('//Tag[.="34"]') = 1

Further, I can get the Name of the control rather than the entire xml with this:

Select theXML.query('//*[Tag="34"]/Label/text()') as 'Control Name'from ViewTable where theXML.exist('//Tag[.="34"]') = 1

How do I get the TabCtrl/Name ? The path from the element that contains the matching Tag could go through 1-n levels of Controls nodes so using the Xpath statement won’t work. The TabCtrl will all ways be a direct child of the Form node

Upvotes: 0

Views: 1620

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

Shred the XML on Form/TabCtrl using nodes(), check the existence of Tag=34 for each shredded node using exist() and finally get the value from Form/TabCtrl/Name with the value() function.

select TC.X.value('(Name/text())[1]', 'nvarchar(100)')
from YourTable as T
  cross apply T.theXML.nodes('/Form/TabCtrl') as TC(X)
where TC.X.exist('*//Tag/text()[. = "34"]') = 1

SQL Fiddle

Upvotes: 1

har07
har07

Reputation: 89305

Possible Solutions :

One possible way to get the text from TabCtrl/Name containing a control that has the Tag of 34 :

Select theXML.query('//TabCtrl[Controls/*/Tag="34"]/Name/text()') as 'Control Name'

Or if you need to get to the <TabCtrl> starting from it's descendant then you can always climb up the tree using parent::element_name or ancestor::element_name or .. :

Select theXML.query('//*[Tag="34"]/ancestor::TabCtrl/Name/text()') as 'Control Name'

Difference between axes mentioned above :

  • parent::element_name : Go one level up to a specific parent element

  • ancestor::element_name : Go one or more level up to a specific ancestor element

  • .. : Go one level up to a parent element with any name

Upvotes: 1

Dan Field
Dan Field

Reputation: 21661

This query will return the TabCtrl/Name for any TabCtrl that has a Controls node that has a grand child Tag that =s 34:

DECLARE @theXML XML = '<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>
</Form>'


Select @theXML.query('//TabCtrl/Controls/*[Tag="34"]/../../Name') as 'Control Name'

Output:

<Name>Tab1</Name>

I'm not 100% sure that's what you're looking for, but this is probably the general pattern you want to follow here, with an additional query if you need to get more deeply nested Tag=34 nodes.

Upvotes: 0

Related Questions