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.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.