如何使用XQuery-sql在XML中查找祖先

I'm working with some xml columns and I use XQuery to find the first nearest ancestor (B) of a specific child (child E) but this error occurs:

我正在使用一些xml列,我使用XQuery查找特定子项(子E)的第一个最近的祖先(B),但是会发生以下错误:

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

Msg 9335,Level 16,State 1,Line 16 XQuery [query()]:不支持XQuery语法'ancestor'。

The depth level of ancestors and the descendants is not static and I want the result to be "B2" I have tried

祖先和后代的深度级别不是静态的,我希望结果是“B2”我尝试过

    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 please?

你能帮我吗?

2 个解决方案

#1

You can try using a different approach to avoid having to call ancestor axis, for example :

您可以尝试使用不同的方法来避免调用祖先轴,例如:

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

brief explanation regarding the predicates being used :

关于正在使用的谓词的简要说明:

[.//E] : ensure that the target B elements have descendant element(s) E

[.//]:确保目标B元素具有后代元素E.

[not(.//B)] : ensure that the target B elements don't have descendant element(s) B, in other words the target B element has to be the inner-most B element having descendant E

[not(.// B)]:确保目标B元素没有后代元素B,换句话说,目标B元素必须是具有后代E的最内部B元素

#2

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

#1