Passing a huge XML string to a stored procedure and parsing it further, use parsed elements as params to call another stored procedure

Question

I am using SQL Server 2016 and want to reuse an existing stored procedure (let's call it ESP) by calling it from a newly-written stored procedure (let's call it NSP).

ESP is an atomic procedure that accepts a few parameters (3 integers, 1 varchar and 1 bit) and returns one row. Hence NSP is supposed to call ESP, say, N times and gather/pile-up all the results the results that it obtains from ESP in a #tempTable. And finally NSP returns this #tempTable to the script which remotely calls it.

The script that calls NSP passes a huge XML string as a parameter (I have provided the sample XML string that contains a few elements). NSP is supposed to use this XML and call ESP until all the element-sets are exhausted. All piled up results are to be returned via temp table. Since XML parsing in a stored procedure doesn't require a loop, I am having difficult time to figure out the working syntax.

I tried writing the stored procedure, but it seems like @xmlString.nodes() function/syntax works only with a SELECT query. I am not able to understand how to use a loop here.

Sample XML string that is used as a param to NSP:

N'<AllTests>
    <Test>
        <TestID>509738</TestID>
        <Status>3</Status>
        <Label>OnWindows</Label>
    </Test>
    <Test>
    <TestID>509737</TestID>
        <Status>3</Status>
        <Label>OnLinux</Label>
    </Test>
    <Test>
        <TestID>516417</TestID>
        <Status>3</Status>
        <Label>OnMac</Label>
    </Test>
    <Test>
        <TestID>516416</TestID>
        <Status>3</Status>
        <Label>OnLinux</Label>
    </Test>
</AllTests>'

NSP is written as follows

Use DebugIssueStorage
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NSP]
    (@eXml AS XML = NULL)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #TempTable
    (
        ValOneReturnedByESP INT NOT NULL,
        ValTwoReturnedByESP INT
    )

    INSERT INTO #TempTable
     EXEC [dbo].[ESP] 
        @TestID         = c.value('(TestID)[1]',             'int'), 
        @Status         = c.value('(Status)[1]',             'int'),
        @Label          = c.value('(Label)[1]',     'varchar(256)'),
     FROM @eXml.nodes('/AllTests/Test') as T(c)

     SELECT * FROM #TempTable
END

Please let me know where is the issue or how to use the looped structure here to make the NSP stored procedure work.

P.S. Actual XML contains around 30k+ Test-tagged objects in the XML. And I have got the error of this sort - DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information

Answer 1

If you need to loop and call a stored procedure for every row in a table or query, you use a CURSOR, although it would probably perform better if you had a version of ESP that operated over a whole set of data, rather than row-by-row.

eg

CREATE OR ALTER PROCEDURE [dbo].[NSP]
(
    @eXml AS XML = NULL
)

AS
BEGIN
/*
declare @doc xml = N'<AllTests>
    <Test>
        <TestID>509738</TestID>
        <Status>3</Status>
        <Label>OnWindows</Label>
    </Test>
    <Test>
    <TestID>509737</TestID>
        <Status>3</Status>
        <Label>OnLinux</Label>
    </Test>
    <Test>
        <TestID>516417</TestID>
        <Status>3</Status>
        <Label>OnMac</Label>
    </Test>
    <Test>
        <TestID>516416</TestID>
        <Status>3</Status>
        <Label>OnLinux</Label>
    </Test>
</AllTests>'

exec NSP @doc

*/
    SET NOCOUNT ON;

     declare @TestID int;
     declare @Status int;
     declare @Label varchar(255);

     declare c cursor local for 
         SELECT
            TestID         = c.value('(TestID)[1]',             'int'), 
            Status         = c.value('(Status)[1]',             'int'),
            Label          = c.value('(Label)[1]',     'varchar(256)')
         FROM @eXml.nodes('/AllTests/Test') as T(c)


    CREATE TABLE #results
    (
        ValOneReturnedByESP INT NOT NULL,
        ValTwoReturnedByESP INT
    )

    open c
    fetch next from c into @TestID, @Status, @Label
    while @@FETCH_STATUS = 0
    begin
        insert into #results
        exec ESP @TestID, @Status, @Label

        fetch next from c into @TestID, @Status, @Label
    end
    close c
    deallocate c

    select * from #results

END

go

create or alter procedure ESP @TestID int, @Status int, @Label varchar(255)
as
begin
  select 1 a, 2 b
end