SQL - 提取具有多个节点的 XML-解网

问：

我正在尝试从我的 SQL 列中的 XML 中提取数据，该列中有多个数据列表，这些数据以两种不同的方式存储。

我只熟悉使用交叉应用来获取单个列表，但是如何为多个节点执行此操作，尤其是当它们处于不同的结构中时？

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 = XC.value('@name', 'VARCHAR(200)'),
    App =  XC2.value('@name', 'VARCHAR(200)'),
    Ent =  XC3.value('.', 'VARCHAR(200)')
FROM 
    @Xml.nodes('//Bundle') AS XT(XC)
CROSS APPLY
    xc.nodes('//Bundle/Profiles/Profile/ApplicationRef/Reference') AS XT2(XC2)
CROSS APPLY
    xc.nodes('//Bundle/Profiles/Profile/Constraints/Filter/Value/List/String') AS XT3(XC3)

我不介意 ent 数据是否在单个列中，但 Bun 和 App 列需要不同

包子	应用程序	耳鼻喉科
x	应用1	ent1
x	应用1	ent2
x	应用2	entA
x	应用2	entB
y	应用1	ent3
y	应用1	ent4型
y	应用3	这
y	应用3	那
y	应用3	因此

SQL XML

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和样本数据填充，即 CREATE 表加上 INSERT T-SQL 语句。（2）你需要做什么，即逻辑和你的代码尝试在T-SQL中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。所有问题都是文本，没有图像。

答：

1赞 Yitzhak Khabinsky 11/8/2023 #1

希望这就是你要找的。

SQL格式

DECLARE @xml XML = 
N'<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  p.value('@name', 'VARCHAR(200)') AS bundle_name
    , c.value('(ApplicationRef/Reference/@name)[1]', 'VARCHAR(200)') AS app_name
    , d.value('(.)[1]', 'VARCHAR(200)') AS constraints_filter
FROM @Xml.nodes('/root/Bundle') t1(p)
CROSS APPLY p.nodes('Profiles/Profile') AS t2(c)
CROSS APPLY c.nodes('Constraints/Filter/@value') AS t3(d)
UNION ALL
SELECT  p.value('@name', 'VARCHAR(200)') AS bundle_name
    , c.value('(ApplicationRef/Reference/@name)[1]', 'VARCHAR(200)') AS app_name
    , e.value('(text())[1]', 'VARCHAR(200)') AS constraints_filter_list
FROM @Xml.nodes('/root/Bundle') t1(p)
CROSS APPLY p.nodes('Profiles/Profile') AS t2(c)
CROSS APPLY c.nodes('Constraints/Filter/Value/List/String') AS t4(e);

输出

bundle_name	app_name	constraints_filter
x	应用1	ent1
x	应用1	ent2
x	应用2	entA
x	应用2	entB
y	应用1	ent3
y	应用1	ent4型
y	应用3	entA
y	应用3	这
y	应用3	那
y	应用3	因此

SQL - 提取具有多个节点的 XML

SQL - extract XML with multiple nodes

评论

评论