T-SQL解析表中XML字段获取子项数据求助

没问题！要实现你想要的结果，我们可以利用SQL Server内置的XML数据类型方法，配合CROSS APPLY来拆分XML中的子节点并提取所需数据。以下是完整的解决方案：

DECLARE @tbl TABLE ( [ItemId] NVARCHAR(250), [Quantity] DECIMAL(18, 2), xmlData XML );
INSERT INTO @tbl VALUES 
('Item1', 3, '<record> <memberList> <itemMember> <quantity>3.0</quantity> <item internalId="553"> </item> </itemMember> <itemMember> <quantity>2.0</quantity> <item internalId="554"> </item> </itemMember> </memberList></record>'), 
('Item2', 5, '<record> <memberList> <itemMember> <quantity>4.0</quantity> <item internalId="53"> </item> </itemMember> <itemMember> <quantity>6.0</quantity> <item internalId="54"> </item> </itemMember> </memberList></record>')

-- 提取子项信息的查询
SELECT 
    t.ItemId,
    t.Quantity,
    -- 提取item节点的internalId属性值
    itemMember.value('(item/@internalId)[1]', 'NVARCHAR(250)') AS ItemChild,
    -- 提取quantity元素的数值并转为整数（和预期结果匹配）
    CAST(itemMember.value('(quantity/text())[1]', 'DECIMAL(18,2)') AS INT) AS QtyChild
FROM @tbl t
-- 拆分xmlData中的每个itemMember节点为单独行
CROSS APPLY t.xmlData.nodes('/record/memberList/itemMember') AS x(itemMember)

代码说明： #

• .nodes()方法：用来将XML字段中的<itemMember>节点拆分成独立的行，这样每个子项都会对应一条记录，关联原表的ItemId和Quantity。
• .value()方法：负责从XML节点中提取具体数据：
  • (item/@internalId)[1]：定位到<item>节点的internalId属性，[1]确保只获取第一个匹配的属性值（避免多匹配时返回错误）。
  • (quantity/text())[1]：获取<quantity>元素的文本内容，先转为DECIMAL(18,2)再转成INT，和你预期结果中的整数格式一致；如果需要保留小数，直接去掉外层的CAST即可。

执行这段查询后，得到的结果就和你给出的@Result表完全一致啦。

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