如何在SQL中使用OPENXML解析含多Undly节点的XML？

嘿，我明白你现在的困扰了——用SQL解析这份XML时，两个<Undly>节点只输出了第一个，没法得到所有组合的预期结果对吧？这是因为你之前的查询没有对多节点集合做行展开处理，而是直接提取了单个节点的值，所以漏掉了后续的Undly节点。下面给你针对主流SQL数据库的具体解决方案：

SQL Server 解决方案 #

在SQL Server里，我们可以用.nodes()方法配合CROSS APPLY来拆分每个多节点集合，自动生成所有组合的笛卡尔积：

DECLARE @xml XML = N'<SecDefUpd> 
<Instrmt Status="1"> 
<Evnt EventTyp="5" Dt="2005-12-19"/> 
<Pty R="22" ID="XASE"> 
<Sub Typ="27" TID="2005-12-17"/> 
</Pty> 
<Pty R="22" ID="XCBO"> 
<Sub Typ="27" TID="2005-12-17"/> 
</Pty> 
</Instrmt> 
<Undly STyp="4" ></Undly> 
<Undly STyp ="5"></Undly> 
</SecDefUpd>';

SELECT 
    Instrmt.value('@Status', 'VARCHAR(10)') AS Status,
    Evnt.value('@EventTyp', 'VARCHAR(10)') AS EventTyp,
    Evnt.value('@Dt', 'DATE') AS Dt,
    Pty.value('@R', 'VARCHAR(10)') AS R,
    Pty.value('@ID', 'VARCHAR(10)') AS ID,
    Sub.value('@Typ', 'VARCHAR(10)') AS Typ,
    Sub.value('@TID', 'DATE') AS TID,
    Undly.value('@STyp', 'VARCHAR(10)') AS STyp
FROM 
    @xml.nodes('/SecDefUpd/Instrmt') AS T1(Instrmt)
CROSS APPLY 
    Instrmt.nodes('Evnt') AS T2(Evnt)
CROSS APPLY 
    Instrmt.nodes('Pty') AS T3(Pty)
CROSS APPLY 
    Pty.nodes('Sub') AS T4(Sub)
CROSS APPLY 
    @xml.nodes('/SecDefUpd/Undly') AS T5(Undly);

关键说明： #

• .nodes()方法会把指定路径下的所有节点拆分成独立的行记录
• CROSS APPLY负责把这些拆分后的行和上层数据做关联，自动生成所有可能的组合，正好匹配你要的4条结果

PostgreSQL 解决方案 #

PostgreSQL使用xmltable函数来拆分XML节点，我们可以先拆分出Instrmt相关的所有Pty/Sub组合，再拆分Undly节点，最后用CROSS JOIN得到全量结果：

WITH xml_data AS (
    SELECT '<SecDefUpd> 
<Instrmt Status="1"> 
<Evnt EventTyp="5" Dt="2005-12-19"/> 
<Pty R="22" ID="XASE"> 
<Sub Typ="27" TID="2005-12-17"/> 
</Pty> 
<Pty R="22" ID="XCBO"> 
<Sub Typ="27" TID="2005-12-17"/> 
</Pty> 
</Instrmt> 
<Undly STyp="4" ></Undly> 
<Undly STyp ="5"></Undly> 
</SecDefUpd>'::XML AS xml_content
),
instrmt_data AS (
    SELECT 
        x.status,
        x.eventtyp,
        x.dt,
        y.r,
        y.id,
        z.typ,
        z.tid
    FROM xml_data d,
         XMLTABLE('/SecDefUpd/Instrmt'
                  PASSING d.xml_content
                  COLUMNS 
                      status VARCHAR(10) PATH '@Status',
                      eventtyp VARCHAR(10) PATH 'Evnt/@EventTyp',
                      dt DATE PATH 'Evnt/@Dt') x,
         XMLTABLE('Pty'
                  PASSING x.*
                  COLUMNS 
                      r VARCHAR(10) PATH '@R',
                      id VARCHAR(10) PATH '@ID') y,
         XMLTABLE('Sub'
                  PASSING y.*
                  COLUMNS 
                      typ VARCHAR(10) PATH '@Typ',
                      tid DATE PATH '@TID') z
),
undly_data AS (
    SELECT 
        styp
    FROM xml_data d,
         XMLTABLE('/SecDefUpd/Undly' 
                  PASSING d.xml_content
                  COLUMNS styp VARCHAR(10) PATH '@STyp')
)
SELECT 
    i.status,
    i.eventtyp,
    i.dt,
    i.r,
    i.id,
    i.typ,
    i.tid,
    u.styp
FROM instrmt_data i
CROSS JOIN undly_data u;

核心思路： #

1. 先通过xmltable拆分出Instrmt下的所有Pty和Sub节点数据，得到2条基础记录
2. 再拆分出Undly的2条节点数据
3. 用CROSS JOIN把两个集合做笛卡尔积，得到最终的4条组合结果

总结 #

你之前的问题本质是没有处理XML中的多节点集合，只是直接提取了第一个节点的值。通过行展开+关联的方式，把每个多节点集合拆分成独立行，就能得到所有预期的组合数据。

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