将VARCHAR列中的XML标签列表转换为逗号分隔列表

Question

我有一个表，其中包含XML标签/值的列表，我需要使用这些列表将其连接到另一个表以检索其实际值并将结果显示为CSV列表。

示例VARCHAR数据：

<choice id="100"/><choice id="101"/><choice id="102"/>

但是，这些值实际上转化为其他值：红色，白色，蓝色。我需要将该列表转换为以下列表：

red,white,blue

作为回顾，“源”表列为VARCHAR，并包含XML属性值的列表，这些值通过将其连接到另一个表来转化为其他值。因此，另一个表具有ID（INT）的主要键，其行为100,101,102。这些行中的每一行分别具有红色，白色，蓝色的值。我希望这足够有意义。

这是设置该方案的DDL：

create table datatable(
  id int,
  data nvarchar(449)
  primary key (id)
);

insert into datatable(id, data)
values(1,'<choice id="100"/><choice id="101"/><choice id="102"/>')
    ,(2,'<choice id="100"/>')
    ,(3,'<choice id="101"/>')
    ,(4,'<choice id="102"/>');

create table choicetable(
    id int,
    choicevalue nvarchar(449)
    primary key (id)
);

insert into choicetable(id, choicevalue)
values(100,'red')
    ,(101,'white')
    ,(102,'blue');

这是我第一次尝试以这种方式解析XML，因此我有点想在哪里开始。另外，我无法控制数据库，我正在从（第三方软件）中检索数据。

Answer 1

如果没有正确的示例数据，就很难给出确切的查询。但是您将执行类似此类

• 用法交叉应用将varchar转换为xml
• 使用使用.nodes将XML进入单独的行。
• 使用.value加入以获取ID属性
• 组，然后使用String_agg加入构造。根据您的情况，您可能不需要 组。

SELECT
  xt.Id,
  STRING_AGG(ot.Value, ',')
FROM XmlTable xt
CROSS APPLY (SELECT CAST(xt.XmlColumn AS xml) ) v(XmlData)
CROSS APPLY v.XmlData.nodes('/choice') x1(choice)
JOIN OtherTable ot ON ot.Id = x1.choice.value('@id','int')
GROUP BY
  xt.Id;

我建议您将XML数据存储在XML键入列中，如果可能的话。