Tuesday, 8 July 2014

Transform multiple xml cells into rows



I have a table with parameters. The parameter value is a 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>'


As a result I would like to have something like this:



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


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



SELECT
@id,
x.ArrayOfInt.value('.','int')
FROM @xml.nodes('//ArrayOfInt/int') x(ArrayOfInt)


Is there a smarter way to do this without cursor?


No comments:

Post a Comment