T-SQL - Convert/Transpose multiple XML node values to a column

Question

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

<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:

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!

marc_s · Accepted Answer · 2014-02-27 12:54:53Z

5

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

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)

answered Feb 27, 2014 at 12:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL - Convert/Transpose multiple XML node values to a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related