CODEWITHSUNDEEP
sql-serverxmlt-sql
xurc
xurc

Reputation: 109

Transform multiple xml cells into rows

I have a table with parameters (id and value). There are some parameters that contains list of integers stored as xml, i.e.

id, val
=========
1, '<ArrayOfInt><int>3</int><int>7</int></ArrayOfInt>'
2, '<ArrayOfInt><int>8</int><int>10</int><int>15</int></ArrayOfInt>'

I would like to get a result like this:

id, val
=======
1, 3
1, 7
2, 8
2, 10
2, 15

It is really simple to do this with temporary table and cursor (but I would like to avoid this). For instance: for each row retrieve xml and:

[...]
WHILE @@FETCH_RESULT = 0
BEGIN
  INSERT INTO #tmp
  SELECT
      @id,
      x.ArrayOfInt.value('.','int')
  FROM @xml.nodes('//ArrayOfInt/int') x(ArrayOfInt)
  FETCH NEXT FROM cur INTO @id, @xml
END
[...]

Is there a smarter way to do this without cursor?

Upvotes: 0

Views: 52

Answers (1)

xurc
xurc

Reputation: 109

It turns out that I was very close to get answer by myself. To get the result I wrote:

declare @params table 
(
  id int identity(1,1),
  val xml
)

insert into @params (val) values ('<ArrayOfInt><int>3</int><int>7</int></ArrayOfInt>')
insert into @params (val) values ('<ArrayOfInt><int>8</int><int>10</int><int>15</int></ArrayOfInt>')


SELECT      
    p.id,
    tab.ArrayOfInt.value('(.)','int') as val
FROM @params p
  CROSS APPLY p.val.nodes('/ArrayOfInt/int') as tab(ArrayOfInt)

The result is as expected.

Upvotes: 1

Related Questions