如何用SQL将单个字段中的XML数据拆分为多行？

问题背景 #

在单个字段存储XML格式数据，需要将XML中的<DailyProperties>节点拆分为多行数据，每个节点对应一行记录。

示例XML结构：

<env>
    <id>id1</id>
    <DailyProperties>
        <date>01/01/2022</date>
        <value>1</value>
    </DailyProperties>
    <DailyProperties>
        <date>05/05/2022</date>
        <value>2</value>
    </DailyProperties>
</env>

期望输出：

ID      DATE            VALUE
id1     01/01/2022      1
id1     05/05/2022      2

之前的SQL语句会返回拼接后的字符串，无法拆分多行：

SELECT
    ID,
    XMLDATA.X.query('/env/DailyProperties/date').value('.', 'varchar(100)') as r_date,
    XMLDATA.X.query('/env/DailyProperties/value').value('.', 'varchar(100)') as r_value
from tableX
outer apply xmlData.nodes('.') as XMLDATA(X)
WHERE ID = 'id1'

执行结果：

ID      R_DATE                  R_VALUE
id01    01/01/202205/05/2022    12

正确实现方法 #

核心是直接针对<DailyProperties>节点进行拆分，让每个节点对应一行记录，再提取节点内的属性值。

正确SQL语句 #

SELECT
    t.ID,
    dp.value('(date/text())[1]', 'varchar(100)') AS DATE,
    dp.value('(value/text())[1]', 'varchar(100)') AS VALUE
FROM tableX t
OUTER APPLY t.xmlData.nodes('/env/DailyProperties') AS Daily(dp)
WHERE t.ID = 'id1'

关键说明 #

• 节点拆分：使用nodes('/env/DailyProperties')将XML中所有<DailyProperties>节点拆分为独立行，每个节点对应一条记录。
• 精准取值：通过dp.value('(date/text())[1]', ...)提取节点内的文本内容，[1]确保只取第一个文本节点，避免自动拼接多个值。
• 关联原表字段：保留原表的ID字段，让拆分后的属性值与原ID关联，实现预期的多行输出。

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