CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
Inm0r74L
Inm0r74L

Reputation: 205

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:

Upvotes: 4

Views: 303

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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)

Upvotes: 0

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56222

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)

Upvotes: 1

Related Questions