Geek Answers Handbook

How to find ancestors in XML using XQuery-sql

I work with some xml columns, and I use XQuery to find the first closest ancestor (B) of a specific child (child E), but this error occurs:

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

The depth level of the ancestors and descendants is not static, and I want the result to be “B2” I tried

DECLARE @x xml SET @x = CAST( '<A> <B name="B1"> <C> <B id="1" name="B2"> <D id="1.1" name="D1"> <E id="1" /> <E id="2" /> </D> </B> </C> </B> </A>' AS xml) SELECT @x.query('data(//E/ancestor::B[1]/@name)')

Can you help me?

+4
source share
2 answers

You can try a different approach to avoid having to name the ancestor axis, for example:

 SELECT @x.query('data(//B[.//E][not(.//B)]/@name)')

brief description of the predicates used:

  • [.//E] : make sure target elements B have descendant element E
  • [not(.//B)] : make sure that target elements of B do not have element (s) of descendant B , in other words, target element B must be the innermost element of B having descendant E
+3
source 
 DECLARE @h int DECLARE @x xml SET @x = CAST( '<A> <B name="B1"> <C> <B id="1" name="B2"> <D id="1.1" name="D1"> <E id="1" /> <E id="2" /> </D> </B> </C> </B> </A>' AS xml) EXEC sp_xml_preparedocument @h OUTPUT, @x SELECT eId, bName FROM OPENXML(@h, '//E', 2) WITH ( eId varchar(5) '@id', bName varchar(5) 'ancestor::B[1]/@name') EXEC sp_xml_removedocument @h
+2
source

More articles:


All Articles