This is how I convert XML string into a Table in SQL 2005:
DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50),
password NVARCHAR(50)
)
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'
INSERT INTO @tempTable
SELECT Tbl.Col.value('@userId', 'INT'),
Tbl.Col.value('@userName', 'NVARCHAR(50)'),
Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM @xml.nodes('//row') Tbl(Col)
--See the table
SELECT * FROM @tempTable
Many thanks Kenny - I have been floundering with this for ages as have obviously many others.
ReplyDeleteNeil Douglas
I spent time on this as well long time ago, and that's why I put it here so it can save others time on this. I am glad that I can help. :)
ReplyDeleteThank you Keeny Hsu.....Good Work :)
DeleteHari
Thanx kenny,I happened to see ur blog while I was blogging for some other Info.
ReplyDeleteGreat keep up good work
Thanks Kenny, this is exactly what I want.
ReplyDeleteThank you very much!
ReplyDeleteHi,
ReplyDeleteI just came across this postings and I'm not sure how to use above to solve my problem. I have a fairly large XML file (165MG) which was exported from MS Access and I need in SQL format so I can upload to the server. How do I use above to do this. Any help will greatly be appreciated.
@Anonymous
ReplyDeleteCreate a stored procedure and take an input parameter @xml. Feed that parameter with your xml file content.
Reference:
http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server
Hey Kenny! Did you worked at Seattle in 2007? Sandeep
ReplyDeleteYes. Are you the Sandeep that I familiar with? :)
ReplyDeleteThanks a bunch, really helpful :)
ReplyDeleteYes, we worked at Saltmine.
ReplyDeleteIt's a small world. :) How's going?
ReplyDeleteThis helped me greatly today.. Thanks very much Kenny
ReplyDeleteThanks a ton Kenny!
ReplyDeleteVery helpful, but what if the column schema for the table were stored in XML as well? That is, I have 2 sets of XML, one is the column definitions for a table, and the other is the data to insert into the table. Is there an SQL solution for this? or is writing a CLR function the best solution?
ReplyDeleteThanks a lot....:)
ReplyDeleteThank you man! You helped a lot!!
ReplyDeleteGreat post.
ReplyDelete