SQL XML - set of return results

Question

I have the following SQL query:

declare @x xml set @x = '<IDs><ID>1</ID><ID>2</ID></IDs>' SELECT @x.query('/IDs/ID') as ID 

This returns the following result:

 ID -------------------- <ID>1</ID><ID>2</ID> 

How can I return this instead:

 ID -- 1 2 

Answer 1

Use this code instead:

 declare @x xml set @x = '<IDs><ID>1</ID><ID>2</ID></IDs>' SELECT ID.value('.', 'int') AS ID FROM @x.nodes('/IDs/ID') as IDS(ID)