如何编写UPDATE查询将文本列中XML属性值提取至新列？

嘿，这事儿不难，SQL Server里可以用XML数据类型的value()方法来提取属性值，再结合类型转换把数据塞进你新建的列里。因为你的XML存在text类型列里，第一步得先把它转成XML类型才能用这些方法。

假设你存储XML的text列名叫XMLContent（记得换成你实际的列名哦），完整的UPDATE语句如下：

UPDATE [dbo].[TimeLapse]
SET 
    [QueryID] = CAST(CAST([XMLContent] AS XML).value('(/TimeLapse/@QueryID)[1]', 'int') AS int),
    [IntervalType] = CAST([XMLContent] AS XML).value('(/TimeLapse/@IntervalType)[1]', 'varchar(10)'),
    [GroupingType] = CAST([XMLContent] AS XML).value('(/TimeLapse/@GroupType)[1]', 'varchar(10)'),
    [Accumulate] = CASE 
                     WHEN CAST([XMLContent] AS XML).value('(/TimeLapse/@Accumulate)[1]', 'char(1)') = 'Y' THEN 1 
                     ELSE 0 
                   END,
    [FrameRate] = CAST(CAST([XMLContent] AS XML).value('(/TimeLapse/@FrameRate)[1]', 'int') AS int),
    [PeriodFixedStartDate] = CAST([XMLContent] AS XML).value('(/TimeLapse/@StartDateTime)[1]', 'datetime')

关键细节说明： #

• text转XML：因为原列是text类型，必须用CAST([XMLContent] AS XML)转换后才能使用XML的内置方法。
• XPath语法：(/TimeLapse/@QueryID)[1]表示定位到根节点TimeLapse的QueryID属性，[1]确保只取第一个匹配的属性（你的示例是单个节点，完全适用）。
• 类型匹配：
  • QueryID和FrameRate是int类型，要把提取到的字符串转成int；
  • Accumulate是bit类型，用CASE把XML里的'Y'/'N'转成1（TRUE）/0（FALSE）；
  • PeriodFixedStartDate对应XML里的StartDateTime，直接转成datetime类型即可。
• 空值处理：如果有些行的XML缺失某个属性，提取结果会是NULL，刚好你的新列大多允许NULL，Accumulate有默认值FALSE，也能兼容。

如果担心转换失败（比如XML格式不合法），可以用TRY_CAST代替CAST，这样转换失败时会返回NULL而不是报错：

UPDATE [dbo].[TimeLapse]
SET 
    [QueryID] = TRY_CAST(TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@QueryID)[1]', 'varchar(20)') AS int),
    [IntervalType] = TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@IntervalType)[1]', 'varchar(10)'),
    [GroupingType] = TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@GroupType)[1]', 'varchar(10)'),
    [Accumulate] = CASE 
                     WHEN TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@Accumulate)[1]', 'char(1)') = 'Y' THEN 1 
                     ELSE 0 
                   END,
    [FrameRate] = TRY_CAST(TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@FrameRate)[1]', 'varchar(20)') AS int),
    [PeriodFixedStartDate] = TRY_CAST(TRY_CAST([XMLContent] AS XML).value('(/TimeLapse/@StartDateTime)[1]', 'varchar(50)') AS datetime)

这样就算有些行的XML有问题，也不会导致整个UPDATE操作失败，只会把对应列设为NULL~

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