/* Reading XML String As Table

Sample XML String
<employees>
    <employee><name>Hubert</name><age>19</age></employee>
    <employee><name>Gaye</name><age>12</age></employee>
</employees>

<employees>
    <employee name="Hubert" age="19" />
    <employee name="Gaye" age="12" />
</employees>

*/

ALTER PROCEDURE [dbo].[spInsertToTest]
    @xml text
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @hdoc int

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
    
    INSERT test
    SELECT *
    FROM OPENXML(@hdoc, '/employees/employee', 3)
        WITH ([name] varchar(50), age int)

    EXEC sp_xml_removedocument @hdoc    

END

Reading XML String As Table

xml

SQL