时间戳

首页 > 解决方案 > 如何从 SQL XML 列中提取值列表

sql - 如何从 SQL XML 列中提取值列表

问题描述

这是我的 XML：

<Triggers>
  <Trigger>
    <Name>DrugName</Name>
    <Values>
      <Value>Meclofenamate</Value>
      <Value>Meloxicam</Value>
      <Value>Vimovo</Value>
      <Value>Nabumetone</Value>
      <Value>Qmiiz</Value>
      <Value>Tolmetin</Value>    
    </Values>
  </Trigger>
  <Trigger>
    <Name>State</Name>
    <Values>
      <Value>MI</Value>
    </Values>
  </Trigger>
  <Trigger>
    <Name>BenefitType</Name>
    <Values>
      <Value>Pharmacy</Value>
    </Values>
  </Trigger>
  <Trigger>
    <Name>LineOfBusiness</Name>
    <Values>
      <Value>Medicaid</Value>
    </Values>
  </Trigger>
</Triggers>

我的目标是获得如下所示的输出：

ID      DrugName        State     BenefitType   LineOfBusiness
6500    Meclofenamate   MI        Pharmacy      Medicaid
6501    Meloxicam       MI        Pharmacy      Medicaid
6502    Vimovo          MI        Pharmacy      Medicaid
6503    Nabumetone      MI        Pharmacy      Medicaid
6504    Qmiiz           MI        Pharmacy      Medicaid
6505    Tolmetin        MI        Pharmacy      Medicaid

在以这种方式组织 XML 的广泛搜索之后，我在 stackoverflow 上找不到任何示例，而我发现、调整和应用的示例导致我在一列中获得所有值的列表（状态值、BenefitType 值等） . 与 DrugName 值混合在一起）。

ID 列不是 XML 的一部分，但我需要在输出中包含它。

这里的表看起来像 XML 列。

标签： sqlsql-serverxml

解决方案

您需要.nodesXML 函数来拆分 Trigger 节点，然后再拆分 Values 行。

要获取节点的值而不是其名称，我们使用text().

为了验证我们Trigger为每一列抓取了正确的节点，我们使用[]谓词来检查（有点像 a where）。

.value需要一个值，所以我们使用[1]来获取第一个节点。

SELECT
    DrugName = drugs.DrugName.value('text()[1]','nvarchar(100)'),
    State = tr.Trigg.value('Trigger[Name/text()="State"][1]/Values[1]/Value[1]/text()[1]', 'nvarchar(100)'),
    BenefitType = tr.Trigg.value('Trigger[Name/text()="BenefitType"][1]/Values[1]/Value[1]/text()[1]', 'nvarchar(100)'),
    LineOfBusiness = tr.Trigg.value('Trigger[Name/text()="LineOfBusiness"][1]/Values[1]/Value[1]/text()[1]', 'nvarchar(100)')
FROM @xml.nodes('/Triggers') tr(Trigg)
OUTER APPLY tr.Trigg.nodes('Trigger[Name/text()="DrugName"][1]/Values/Value') drugs(DrugName)

推荐阅读