2007/12/10

Convert xml to table in SQL 2005

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 

17 comments:

  1. Many thanks Kenny - I have been floundering with this for ages as have obviously many others.

    Neil Douglas

    ReplyDelete
  2. 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. :)

    ReplyDelete
    Replies
    1. Thank you Keeny Hsu.....Good Work :)
      Hari

      Delete
  3. Thanx kenny,I happened to see ur blog while I was blogging for some other Info.
    Great keep up good work

    ReplyDelete
  4. Thanks Kenny, this is exactly what I want.

    ReplyDelete
  5. Hi,
    I 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.

    ReplyDelete
  6. @Anonymous

    Create 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

    ReplyDelete
  7. Hey Kenny! Did you worked at Seattle in 2007? Sandeep

    ReplyDelete
  8. Yes. Are you the Sandeep that I familiar with? :)

    ReplyDelete
  9. Thanks a bunch, really helpful :)

    ReplyDelete
  10. Yes, we worked at Saltmine.

    ReplyDelete
  11. It's a small world. :) How's going?

    ReplyDelete
  12. This helped me greatly today.. Thanks very much Kenny

    ReplyDelete
  13. Thanks a ton Kenny!

    ReplyDelete
  14. Very 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?

    ReplyDelete