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

debugcn 发表于 Dev

文化力量

这是我的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列。

查理菲斯

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

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

为了验证我们Trigger为每列都获取了正确的节点，我们使用[]谓词进行检查（有点像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)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。