MS SQL 2008 - 如何将XML值转换为MS SQL表中的行集合？

Question

我在我的 MS SQL 表格列中有一个名为“XDATA”的值，如下所示：

<root><KV><Key>1</Key><Value>A</Value></KV>
    <KV><Key>2</Key><Value>B</Value></KV>
    <KV><Key>3</Key><Value>C</Value></KV></root>

我希望能够像这样获取行的值:

KEY, VALUE
1,A
2,B
3,C

到目前为止，我已经写了这个：

DECLARE @a1  xml
    declare @x xml;
    set @x = '<root><KV><Key>1</Key><Value>A</Value></KV>
        <KV><Key>2</Key><Value>B</Value></KV>
        <KV><Key>3</Key><Value>C</Value></KV></root>';
   SELECT @a1 = @x.query('(/root/KV/Key/text())')
   select @a1

但是我拿回的是123。并不是三行两列的形式。我该如何才能拿回三行，每行两列的形式：

KEY, VALUE
    1,A
    2,B
    3,C

谢谢

- InfoLearner

Answer 1

使用 nodes() 函数将 XML 解析为行，并使用 value() 函数获取值。

select 
  T.N.value('Key[1]', 'int') as [Key],
  T.N.value('Value[1]', 'varchar(10)') as Value
from @x.nodes('/root/KV') as T(N)

- Mikael Eriksson

---

很好。如果我想要一个表的列，例如 Information 表的 Data 列，而不是 @x 怎么办？ - InfoLearner

@KnowledgeSeeker - 你需要将表格放在from子句中，并对XML列执行cross apply。请参考这个答案。 - Mikael Eriksson