Posted By

hubertd on 03/23/10


Tagged

xml openxml spxmlpreparedocument


Versions (?)

Reading XML String As Table


 / Published in: SQL
 

  1. /* Reading XML String As Table
  2.  
  3. Sample XML String
  4. <employees>
  5.   <employee><name>Hubert</name><age>19</age></employee>
  6.   <employee><name>Gaye</name><age>12</age></employee>
  7. </employees>
  8.  
  9. <employees>
  10.   <employee name="Hubert" age="19" />
  11.   <employee name="Gaye" age="12" />
  12. </employees>
  13.  
  14. */
  15.  
  16. ALTER PROCEDURE [dbo].[spInsertToTest]
  17. @xml text
  18. AS
  19. BEGIN
  20. SET NOCOUNT ON;
  21.  
  22. DECLARE @hdoc int
  23.  
  24. EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
  25.  
  26. INSERT test
  27. SELECT *
  28. FROM OPENXML(@hdoc, '/employees/employee', 3)
  29. WITH ([name] varchar(50), age int)
  30.  
  31. EXEC sp_xml_removedocument @hdoc
  32.  
  33. END

Report this snippet  

You need to login to post a comment.