SQL解析含重复标签的XML：层级回溯与扁平化实现问题

针对你用OPENXML解析带重复<Dsclsr>和<AdrLine>标签的XML时遇到的层级关联问题，推荐两种解决方案，优先使用原生XQuery方法，更高效简洁：

先明确示例XML结构（匹配你的描述） #

假设你的XML结构如下：

<Root>
  <SfkpgAcctAndHldgs>
    <SfkpgAcct>ACC123</SfkpgAcct>
    <HldgInfo>Holdings_Info</HldgInfo>
    <Dsclsr>
      <DsclsrId>D001</DsclsrId>
      <DsclsrText>Disclosure 1</DsclsrText>
      <AdrLine>Line 1 of D001</AdrLine>
      <AdrLine>Line 2 of D001</AdrLine>
    </Dsclsr>
    <Dsclsr>
      <DsclsrId>D002</DsclsrId>
      <DsclsrText>Disclosure 2</DsclsrText>
      <AdrLine>Line 1 of D002</AdrLine>
    </Dsclsr>
  </SfkpgAcctAndHldgs>
  <SfkpgAcctAndHldgs>
    <SfkpgAcct>ACC456</SfkpgAcct>
    <HldgInfo>Another Holdings</HldgInfo>
    <Dsclsr>
      <DsclsrId>D003</DsclsrId>
      <DsclsrText>Disclosure 3</DsclsrText>
      <AdrLine>Line A of D003</AdrLine>
      <AdrLine>Line B of D003</AdrLine>
      <AdrLine>Line C of D003</AdrLine>
    </Dsclsr>
  </SfkpgAcctAndHldgs>
</Root>

方案1：使用原生XQuery（推荐） #

SQL Server 2005及以后支持的.nodes()和.value()方法，能直接嵌套处理多层重复节点，一次性生成扁平化结构，自动关联所有层级字段：

DECLARE @xml XML = '上述示例XML内容'

SELECT
    -- 上层<SfkpgAcctAndHldgs>字段
    SfkpgAcct = acct.value('(SfkpgAcct)[1]', 'VARCHAR(50)'),
    HldgInfo = acct.value('(HldgInfo)[1]', 'VARCHAR(100)'),
    -- <Dsclsr>层级字段
    DsclsrId = dsclsr.value('(DsclsrId)[1]', 'VARCHAR(50)'),
    DsclsrText = dsclsr.value('(DsclsrText)[1]', 'VARCHAR(100)'),
    -- <AdrLine>层级字段
    AdrLine = adr.value('.', 'VARCHAR(200)'),
    -- 可选：生成自定义关联ID
    ParentAcctId = 'ACC_' + acct.value('(SfkpgAcct)[1]', 'VARCHAR(50)'),
    DsclsrUniqueId = 'DSCL_' + acct.value('(SfkpgAcct)[1]', 'VARCHAR(50)') + '_' + dsclsr.value('(DsclsrId)[1]', 'VARCHAR(50)')
FROM @xml.nodes('/Root/SfkpgAcctAndHldgs') AS AcctNodes(acct)
CROSS APPLY acct.nodes('Dsclsr') AS DsclsrNodes(dsclsr)
CROSS APPLY dsclsr.nodes('AdrLine') AS AdrNodes(adr)

方案优势： #

• 自动遍历所有重复的<Dsclsr>和<AdrLine>，不会遗漏
• 直接关联上层<SfkpgAcctAndHldgs>的所有字段，无需手动回溯
• 代码简洁，性能优于OPENXML（原生XML引擎处理）

方案2：改进OPENXML用法（兼容旧版本） #

如果必须使用OPENXML，需要先捕获上层节点的所有子节点XML片段，再二次解析关联：

DECLARE @xml XML = '上述示例XML内容'
DECLARE @docHandle INT
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml

-- 第一步：获取<SfkpgAcctAndHldgs>节点及所有<Dsclsr>的XML片段
DECLARE @parentData TABLE (
    SfkpgAcct VARCHAR(50),
    HldgInfo VARCHAR(100),
    DsclsrXml XML
)
INSERT INTO @parentData
SELECT 
    SfkpgAcct,
    HldgInfo,
    dsclsr.query('.')
FROM OPENXML(@docHandle, '/Root/SfkpgAcctAndHldgs', 2)
WITH (
    SfkpgAcct VARCHAR(50),
    HldgInfo VARCHAR(100),
    dsclsr XML 'Dsclsr'  -- 捕获所有<Dsclsr>为XML片段
)

-- 第二步：解析<Dsclsr>并关联上层字段，再进一步解析<AdrLine>
SELECT
    pd.SfkpgAcct,
    pd.HldgInfo,
    DsclsrId = x.value('(DsclsrId)[1]', 'VARCHAR(50)'),
    DsclsrText = x.value('(DsclsrText)[1]', 'VARCHAR(100)'),
    AdrLine = adr.value('.', 'VARCHAR(200)')
FROM @parentData pd
CROSS APPLY pd.DsclsrXml.nodes('/Dsclsr') AS dsclsr(x)
CROSS APPLY x.nodes('AdrLine') AS adrNodes(adr)

EXEC sp_xml_removedocument @docHandle

关键改进点： #

• 在OPENXML的WITH子句中用XML类型捕获所有重复的<Dsclsr>节点，而不是只取单个字段
• 通过CROSS APPLY二次解析XML片段，实现层级关联

问题根源说明 #

OPENXML默认仅提取第一个匹配的子节点，因此直接查询<SfkpgAcctAndHldgs>时只能拿到第一个<Dsclsr>；而XQuery的.nodes()方法会遍历所有匹配的节点，天然支持多层嵌套的关联查询，是处理此类场景的最优选择。

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