Transact SQL – XQuery – Ancestor

Background

Playing around with XML, but dug in a quick sand.

XML

Here is the XML

Image

foo.xml

Code

Code

<root>                                                                                                             
  <foo id="0">                                                                                                       
	<foo id="1">                                                                                                   
	  <bar attr="xxx" />                                                                                         
	</foo>                                                                                                         
	<foo id="2">                                                                                                   
	  <bar attr="sam" />                                                                                         
	</foo>                                                                                                         
	<foo id="3">                                                                                                   
	  <tar>                                                                                                      
		<bar attr="samuel" />                                                                                     
	  </tar>                                                                                                     
	</foo>                                                                                                         
  </foo>                                                                                                             
</root>

Code

Find Nodes whose element matches specific value

SQL


declare @id int

set @id = 3

SELECT
         [id] = item.value
                    (
                          '@id'
                        , 'INT'
                    ) 

FROM @foo.nodes('//foo') t (item)

WHERE item.value('@id', 'INT') = @id

Output

XPath.attribute.value.is.3

Pass X PATH :- Child::bar/Attribute

SQL


-- child//bar/@attr=sam
SELECT
         [element.attribute.sam]
            = t.item.query('.')

FROM @foo.nodes('//*[child::bar[@attr=''sam'']]') t (item)

Output

XPath.attribute.value.is.sam

Find Descendants

SQL


-- descendant
SELECT
         [element.descendant]
            = t.item.query('.')

FROM @foo.nodes('//foo[@id=2]/descendant::*[position()=1]') t (item)

Output

XPath.element.descendant

Find Ascendants

SQL


-- ancestor
SELECT
         [element]
            = t.item.query('.')

FROM @foo.nodes('//bar[@attr=''sam'']/ancestor::*[position()=1]') t (item)

Output

Output – Textual


Msg 9335, Level 16, State 1, Line 61
XQuery [nodes()]: The XQuery syntax 'ancestor' is not supported.

Output – Image

XPath.element.ascendant

Source Code Control

Git Hub

TransactSQLXQuery/ancenstor/
Link

 

Summary

Transact SQL/XQuery does not support the ancestor function.

Referenced Work

  1. Stack Overflow
    • XPath to find nearest ancestor element that contains an element that has an attribute with a certain value
      Link
    • xpath: find a node that has a given attribute whose value contains a string
      Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s