在SQL Server中将XML格式的WordPress数据解析为行数据

步骤1：转换字段类型（可选） #

如果原字段是varchar/nvarchar类型，可永久修改为XML类型以便后续直接操作：

ALTER TABLE wp_posts ALTER COLUMN category_xml XML;

若不想改动原字段，可在查询时临时转换类型。

步骤2：解析XML为多行结构化数据 #

利用SQL Server的XML节点方法nodes()拆分每个<category>节点，再通过value()提取对应列的值。假设你的XML结构如下（根节点为<categories>，每个子节点<category>包含Domain/NiceName属性和Contents子节点）：

Tech articles and tutorials Lifestyle tips and stories

对应的查询语句：

SELECT
    -- 提取category节点的Domain属性
    c.value('@Domain', 'NVARCHAR(255)') AS Domain,
    -- 提取category节点的NiceName属性
    c.value('@NiceName', 'NVARCHAR(255)') AS NiceName,
    -- 提取Contents子节点的文本内容
    c.value('(Contents/text())[1]', 'NVARCHAR(MAX)') AS Contents
FROM wp_posts
-- 拆分每个category节点为单独行
CROSS APPLY category_xml.nodes('/categories/category') AS t(c);

处理格式无效的XML #

如果存在格式不合法的XML数据，使用TRY_CAST避免查询中断，同时过滤无效行：

SELECT
    c.value('@Domain', 'NVARCHAR(255)') AS Domain,
    c.value('@NiceName', 'NVARCHAR(255)') AS NiceName,
    c.value('(Contents/text())[1]', 'NVARCHAR(MAX)') AS Contents
FROM wp_posts
CROSS APPLY TRY_CAST(category_xml AS XML).nodes('/categories/category') AS t(c)
-- 过滤掉无法转换为XML的行
WHERE TRY_CAST(category_xml AS XML) IS NOT NULL;

注意事项 #

• 若你的XML结构与示例不同（比如Contents是属性而非子节点），需调整XPath表达式：例如c.value('@Contents', 'NVARCHAR(MAX)')
• 根据实际数据长度调整NVARCHAR的长度参数，避免截断内容

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