SQL提取多节点XML数据：不同结构节点的处理方法问询

你的核心问题是没有正确建立XML节点的层级关联，同时遗漏了对两种不同Ent存储方式的处理。原代码使用绝对路径导致跨Bundle关联数据，且只处理了嵌套String节点的情况，无法覆盖直接用属性存储的Ent值。

修正后的SQL代码 #

DECLARE @XML XML = 
'
<root>
<Bundle name="x">
<Profiles>
    <Profile>
        <ApplicationRef>
            <Reference class="a" name="application1"/>
        </ApplicationRef>
        <Constraints>
            <Filter property="e" value="ent1"/>
            <Filter property="e" value="ent2"/>
        </Constraints>
    </Profile>
    <Profile>
        <ApplicationRef>
            <Reference class="a" name="application2"/>
        </ApplicationRef>
        <Constraints>
            <Filter property="e" value="entA"/>
            <Filter property="e" value="entB"/>
        </Constraints>
    </Profile>
</Profiles>
</Bundle>
<Bundle name="y">
<Profiles>
    <Profile>
        <ApplicationRef>
            <Reference class="a" name="application1"/>
        </ApplicationRef>
        <Constraints>
            <Filter property="e" value="ent3"/>
            <Filter property="e" value="ent4"/>
        </Constraints>
    </Profile>
    <Profile>
        <ApplicationRef>
            <Reference class="a" name="application3"/>
        </ApplicationRef>
        <Constraints>
            <Filter property="m" value="entA">
                <Value>
                    <List>
                        <String>this</String>
                        <String>that</String>
                        <String>thus</String>
                    </List>
                </Value>
            </Filter>
        </Constraints>
    </Profile>
</Profiles>
</Bundle>
</root>
'

SELECT
    Bun = Bundle.value('@name', 'VARCHAR(200)'),
    App = Profile.value('(ApplicationRef/Reference/@name)[1]', 'VARCHAR(200)'),
    Ent = COALESCE(String.value('.', 'VARCHAR(200)'), Filter.value('@value', 'VARCHAR(200)'))
FROM 
    @Xml.nodes('//Bundle') AS XT(Bundle)
CROSS APPLY
    Bundle.nodes('./Profiles/Profile') AS XT2(Profile)
CROSS APPLY
    Profile.nodes('./Constraints/Filter') AS XT3(Filter)
OUTER APPLY
    Filter.nodes('./Value/List/String') AS XT4(String)
WHERE
    COALESCE(String.value('.', 'VARCHAR(200)'), Filter.value('@value', 'VARCHAR(200)')) IS NOT NULL

代码逻辑说明 #

1. 层级关联：使用相对路径（如./Profiles/Profile）替代绝对路径，确保每个Bundle只关联自身下的Profile，避免跨节点数据混乱。
2. 双结构兼容：
   • 对带有嵌套String节点的Filter，通过OUTER APPLY遍历每个String提取文本内容。
   • 对直接用@value属性存储的Filter，通过COALESCE自动补取属性值，适配两种不同的存储结构。
3. 精准取值：(ApplicationRef/Reference/@name)[1]确保从每个Profile中准确提取唯一的应用名称。

执行结果 #

内容的提问来源于stack exchange，提问作者quad4x