Sql iteration over XML

I'm trying to read xml data field from a table and insert this data in another database. The XML document looks like this:

<Master>
   <UserIds>
       <id>1</id>
       <id>2</id>
       <id>3</id>
       <id>4</id>
  </UserIds>
</Master>

My idea was get 1 id and insert, get another and insert that, and so on. I tried with a xquery but the best I can get is all the data together but I need to insert the id separated :/

Any help? D:

Answers


declare @xml xml = 
'<Master>
   <UserIds>
       <id>1</id>
       <id>2</id>
       <id>3</id>
       <id>4</id>
  </UserIds>
</Master>
'

insert into YourTable(ID)
select T.N.value('.', 'int')
from @xml.nodes('/Master/UserIds/id') as T(N)

DECLARE @x xml
SET @x = '<Master>
   <UserIds>
       <id>1</id>
       <id>2</id>
       <id>3</id>
       <id>4</id>
  </UserIds>
</Master>'

INSERT TableName
    SELECT T.c.value('.', 'int' )
    FROM @x.nodes('//id') T(c)

Need Your Help

For those running web apps and websites with transactional emails, how much do they affect your server?

email smtp email-integration

I'm doing a little research on the matter, because I am genuinely curious how do transactional emails affect server load and resources? I guess to help answer the question, I'm looking for the foll...

Twitter Bootstrap: navbar button not showing or toggling collapse

twitter-bootstrap collapse navbar twitter-bootstrap-rails

I'm adapting Twitter's Bootstrap to my Ruby on Rails tutorial project (By Michael Hartl) and the upper navigation isn't collapsing.