时间戳

首页 > 解决方案 > 编写 SQL Server 查询以将 XML 格式的数据解压缩到单独的列中

sql - 编写 SQL Server 查询以将 XML 格式的数据解压缩到单独的列中

问题描述

我有一个名为 EventData 的表。该表有一个 XML 类型的列，列名为 XmlData。

XmlData 包含以下格式的数据：

<Data>
  <Head>
    <Site>Site1</Site>
    <SourceID>9A2AB8FD-3BED-4BFA-9A61-B9B41CC0F706</SourceID>
    <SourceDescription>KU11</SourceDescription>
    <TimeStamp>2021-03-16T14:24:21.757Z</TimeStamp>
  </Head>
  <Details Template="EPMS.IProductionMachine">
    <Item>
      <Field>UnitMode</Field>
      <Value Type="Int32">1</Value>
    </Item>
    <Item>
      <Field>BatchId</Field>
      <Value Type="String">210316029</Value>
    </Item>
    <Item>
      <Field>ProdProcessedCount</Field>
      <Value Type="Int32">2136</Value>
    </Item>
    <Item>
      <Field>AlarmEventId</Field>
      <Values />
    </Item>
    <Item>
      <Field>ProductId</Field>
      <Value Type="String">6824</Value>
    </Item>
  </Details>
</Data>

我如何编写一个 SQL 查询，将这些数据作为单个列返回？

列名：[SourceID, TimeStamp, UnitMode, BatchId, ProdProcessedCount, AlarmEventId, ProductId]

标签： sqlsql-serverxmlxquery

解决方案

前几列非常简单：(Data/Head/SourceID/text())[1]非常直截了当。

其他人参与更多：

(Data/Details/Item[Field/text()="BatchId"]/Value/text())[1]

这表示要过滤Item节点，使其具有匹配的名为的子节点Field，其内部文本为BatchId.

AlarmEventId不清楚：我们是否有多个节点并且我们想要聚合它们，或者我们只有一个。我已经给出了两种选择。

请注意，.value需要一个单例节点，因此您只需要使用[1]来获取 forst 结果。

SELECT 
  SourceId     = x.xCol.value('(Data/Head/SourceID/text())[1]','uniqueidentifier'),
  [TimeStamp]  = x.xCol.value('(Data/Head/TimeStamp/text())[1]','datetime'),
  UnitMode     = x.xCol.value('(Data/Details/Item[Field/text()="UnitMode"]/Value/text())[1]', 'int'),
  BatchId      = x.xCol.value('(Data/Details/Item[Field/text()="BatchId"]/Value/text())[1]', 'varchar(100)'),
  ProdProcessedCount= x.xCol.value('(/Data/Details/Item[Field/text()="ProdProcessedCount"]/Value/text())[1]', 'int'),
  AlarmEventIdAll = x.xCol.value('(for $a in Data/Details/Item[Field/text()="AlarmEventId"]/Values return concat(text()[1], "; "))[1]', 'varchar(max)'),
  AlarmEventId = x.xCol.value('(Data/Details/Item[Field/text()="AlarmEventId"]/Values/text())[1]', 'int'),
  ProductId    = x.xCol.value('(Data/Details/Item[Field/text()="ProductId"]/Value/text())[1]', 'varchar(100)')
FROM myTable x

推荐阅读