Post a Comment On: Blogpad

XML:



50
Jon
Doe
A


99
Jon
Connor
A


1235
James
Bond
B



SQL:

declare @Clients xml

set @Clients =
'

50
Jon
Doe
A


99
Jon
Connor
A


1235
James
Bond
B

'

declare @clientsTable as table
(
ID int,
Name varchar(20),
LastName varchar(20),
Class char(1)
)

-- IMPORTANT:
-- value in col.value MUST be lowercase, otherwise this INSERT will fail.
INSERT INTO @clientsTable (ID
,Name
,LastName
,Class)
SELECT col.value('id[1]', 'int') AS ID
,col.value('name[1]', 'varchar(20)') AS Name
,col.value('lastname[1]', 'varchar(20)') AS LastName
,col.value('class[1]', 'char(1)') AS Class
FROM @Clients.nodes('//Client') tab(col)


select * from @clientsTable

Result:

ID Name LastName Class
----------- -------------------- -------------------- -----
50 Jon Doe A
99 Jon Connor A
1235 James Bond B