SQL Server解析表中XML列提取CustomGroups数据生成新表

前提说明 #

你提到的XML列结构参考示例如下，对应解析逻辑匹配该结构：

<IGPosEvtHallStruct>
  <!-- 其他非业务节点省略 -->
  <CustomGroups>
    <Group Id="组ID" GroupSeatsId="组座位ID">
      <Places>
        <Place Id="座位ID"/>
      </Places>
    </Group>
  </CustomGroups>
</IGPosEvtHallStruct>

核心实现逻辑 #

使用SQL Server内置的XML数据处理方法完成解析：

1. 用nodes()方法拆分XML节点，分别拆分Group节点和每个Group下的Place节点
2. 用value()方法提取节点的属性值
3. 通过SELECT ... INTO语法直接生成关联后的新表

完整SQL代码 #

-- 执行前先替换代码中的原表名、新表名，以及属性值的匹配类型
SELECT
  ot.ID,
  ot.DS_RowGuid,
  ot.PCScreenStructureVariantID,
  GroupID = T1.GroupNode.value('@Id', 'INT'), -- 可根据实际Id的类型调整为VARCHAR等
  GroupSeatsID = T1.GroupNode.value('@GroupSeatsId', 'VARCHAR(200)'),
  PlaceId = T2.PlaceNode.value('@Id', 'VARCHAR(200)')
INTO YourNewTableName -- 替换为你要创建的新表名称
FROM YourOriginalTableName ot -- 替换为存储XML的原表名称
-- 拆分所有Group节点
CROSS APPLY ot.Xml.nodes('/IGPosEvtHallStruct/CustomGroups/Group') AS T1(GroupNode)
-- 拆分每个Group下的所有Place节点
CROSS APPLY T1.GroupNode.nodes('Places/Place') AS T2(PlaceNode)

常见适配场景 #

• 如果原表中Xml字段存储类型为NVARCHAR/VARCHAR而非XML类型，将nodes方法前的ot.Xml替换为CAST(ot.Xml AS XML)即可
• 如果需要保留无匹配Group/Place节点的原表行，将CROSS APPLY替换为OUTER APPLY，对应解析出的XML字段会返回NULL
• 执行生成新表操作前，可先注释掉INTO YourNewTableName行，执行查询确认解析结果符合预期后再创建新表

内容的提问来源于stack exchange，提问作者Fareed Abdul Hameed