0
declare @prueba xml 
set @prueba = '<obligaciones> 
            <obligacion> <Id>51</Id> </obligacion>
            <obligacion>  <Id>52</Id> </obligacion>
            <obligacion>  <Id>53</Id> </obligacion> 
           </obligaciones>'

I would like to get the following table from XML

select Id from @prueba....


51
52
53

Can sombody help with the query?

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Hugo
  • 167
  • 1
  • 2
  • 8
  • http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 already answered – indiPy Jan 02 '13 at 14:30

1 Answers1

1

Have a look at the xml Data Type Methods

select T.N.value('(text())[1]', 'int') as Id
from @prueba.nodes('/obligaciones/obligacion/Id') as T(N)

Update:

With multiple values in the obligaciones node you can do like this:

declare @prueba xml set @prueba = '
<obligaciones>
  <obligacion>
    <id>51</id>
    <TipoCancelacionId>1</TipoCancelacionId>
  </obligacion>
  <obligacion>
    <id>52</id>
    <TipoCancelacionId>1</TipoCancelacionId>
  </obligacion>
  <obligacion>
    <id>53</id>
    <TipoCancelacionId>1</TipoCancelacionId>
  </obligacion>
</obligaciones>'

select T.N.value('(id/text())[1]', 'int') as Id,
       T.N.value('(TipoCancelacionId/text())[1]', 'int') as TipoCancelacionId
from @prueba.nodes('/obligaciones/obligacion') as T(N)

Result:

Id          TipoCancelacionId
----------- -----------------
51          1
52          1
53          1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Having 2 parameters in the XML, For example `declare @prueba xml set @prueba = ' 51 1 52 1 53 1 ' ` Any ideas? – Hugo Jan 02 '13 at 15:34