sql - 如何创建更新查询以将存储为文本的 xml 值提取到新列中

Question

我有一个将 XML 存储在文本列中的现有表，因为不再需要这种格式，所以我为每个属性创建了列。

XML如下，只是一个节点：

<TimeLapse QueryID="308" IntervalType="1" GroupType="C" Accumulate="Y" FrameRate="2" StartDateTime="2009-05-20T00:00:00.0000000" />

以下是新列：

ALTER TABLE [dbo].[TimeLapse] 
ADD
    [QueryID] [int] null,
    [IntervalType] [varchar] (10) null,
    [GroupingType] [varchar](10) null,
    [Accumulate] [bit] default 'FALSE',
    [FrameRate] [int] null,
    [PeriodFixedStartDate] [datetime] NULL

我需要执行什么更新查询才能将 XML 提取到新列中？

标签： sqlsql-serverxml

Answer 1

就像评论中提到的那样，您只需要使用 XQUERY 来提取值：

CREATE TABLE dbo.TimeLapse (NotXMLColumn varchar(MAX));
GO

INSERT INTO dbo.TimeLapse (NotXMLColumn)
VALUES('<TimeLapse QueryID="308" IntervalType="1" GroupType="C" Accumulate="Y" FrameRate="2" StartDateTime="2009-05-20T00:00:00.0000000" />')
GO

ALTER TABLE [dbo].[TimeLapse] 
ADD
    [QueryID] [int] null,
    [IntervalType] [varchar] (10) null,
    [GroupingType] [varchar](10) null,
    [Accumulate] [bit] default 'FALSE',
    [FrameRate] [int] null,
    [PeriodFixedStartDate] [smalldatetime] NULL;
GO

UPDATE TL
SET QueryID = XMLColumn.value('(TimeLapse/@QueryID)[1]','int'),
    [IntervalType] = XMLColumn.value('(TimeLapse/@IntervalType)[1]','varchar(10)'),
    [GroupingType] = XMLColumn.value('(TimeLapse/@GroupType)[1]','varchar(10)'),
    [Accumulate] = CASE XMLColumn.value('(TimeLapse/@Accumulate)[1]','char(1)') WHEN 'Y' THEN 1 ELSE 0 END,
    [FrameRate] = XMLColumn.value('(TimeLapse/@FrameRate)[1]','int'),
    [PeriodFixedStartDate] = XMLColumn.value('(TimeLapse/@StartDateTime)[1]','smalldatetime')
FROM dbo.TimeLapse TL
     CROSS APPLY (VALUES(CONVERT(xml,NotXMLColumn)))V(XMLColumn);

GO

SELECT *
FROM dbo.TimeLapse;

DB<>小提琴