将存储在varchar列中的XML数据转换为表格格式

错误原因分析 #

1. Msg 107：语法写法错误，TableName.Rate.nodes不符合SQL规范，必须先指定表对象，再对转换后的XML列调用方法。
2. Msg 9506：nodes方法仅支持XML类型列，你的Rate列是VARCHAR类型，必须先转换为XML类型才能使用XML相关方法。

正确实现代码 #

SELECT
    rate_node.value('(SituationID)[1]', 'varchar(10)') AS [ID]
FROM 
    TableName
CROSS APPLY 
    CAST(Rate AS XML).nodes('/ArrayOfRate/Rate') AS rate_nodes(rate_node)

代码说明 #

• CAST(Rate AS XML)：将VARCHAR类型的Rate列转换为XML类型，满足nodes方法的调用要求。
• CROSS APPLY：将表中每一行的XML数据拆分为对应的节点行，实现XML到表格的展开。
• nodes('/ArrayOfRate/Rate')：通过XPath定位到根节点ArrayOfRate下的所有Rate子节点，每个节点生成一条独立记录。
• rate_node.value(...)：从每个Rate节点中提取SituationID字段的值，转换为指定的VARCHAR类型。

注意事项 #

如果转换XML时出现报错，需检查：

• 存储的XML格式是否合法（是否存在未转义的特殊字符，比如&、<等）。
• 若XML采用UTF-8编码，可使用CONVERT(xml, Rate, 2)进行转换，第二个参数2用于识别UTF-8编码的XML内容。

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