Advanced Search

Reading XML String As Table

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.