将多个XML节点值转换为一个列。

I have an simple XML field in a table which stores one or more values within it's nodes:

我在表中有一个简单的XML字段，它在节点中存储一个或多个值:

<MultiSelect>
  <ItemKey>38</ItemKey>
  <ItemKey>36</ItemKey>
</MultiSelect>

How do I query this field in such a way that these values are extracted to a column in the result?

如何以将这些值提取到结果中的列的方式查询该字段?

Source data:

源数据:

RowKey    Format
-----------------
1         <MultiSelect><ItemKey>40</ItemKey></MultiSelect>
2         <MultiSelect><ItemKey>40</ItemKey><ItemKey>36</ItemKey></MultiSelect>

Aiming for result:

目标结果:

RowKey   ItemKey
----------------
1        40
2        40
2        36

I've got close to where I want to be with the query below, but this is only returning the NAME of the XML nodes rather than the values within them:

我已经接近了下面的查询，但这只是返回XML节点的名称，而不是其中的值:

SELECT 
    [RowKey],
    x.y.value('local-name(.)', 'varchar(max)') AS ItemKey

FROM
[tbl_MyDataTable] AS t
    cross apply t.[Format].nodes('MultiSelect/ItemKey') x(y)

Result:

结果:

RowKey   ItemKey
----------------
1       ItemKey
2       ItemKey
2       ItemKey

Any suggestions greatfully recieved!

任何建议感激收到!

1 个解决方案

#1

You're close - try this (since those are all INT numerical values, I'd recommend using INT in your XQuery):

您很接近—试试这个(因为这些都是INT数值，所以我建议您在XQuery中使用INT):

SELECT 
    [RowKey],
    x.y.value('(.)[1]', 'INT') AS ItemKey
FROM
    dbo.[tbl_MyDataTable] AS t
CROSS APPLY
    t.[Format].nodes('/MultiSelect/ItemKey') x(y)

Basically, by selecting (.)[1], you're selecting the value of the node - not it's name (which is ItemKey)

基本上，通过选择(.)[1]，您选择的是节点的值——而不是它的名称(它是ItemKey)

#1