How to select xml in 1 stored procedure and pass selected nodes as parameters into another procedure

Question

I have a stored proc that does inserts of “people”. I have an xml document with a bunch of people I want to insert. I want to call my stored proc like below and expect the stored proc to be called for each person in the xml. It is telling me that the stored proc “expects a parameter of @Id” and is failing. @Id is the first param and it appears that my syntax is not allowed. Is there a way to do this without iterating over each person in a cursor? I am using SQL Server 2005.

EXEC Stored_Procedure_That_Inserts_People
SELECT Node.value('Id[1]', 'Int') AS Id
,Node.value('FirstName[1]', 'varchar(50)') AS FirstName
,Node.value('LastName[1]', 'varchar(50)') AS LastName
,Node.value('MI[1]', 'char(1)') AS MI
FROM @PeopleXML.nodes('/ArrayOfPeople/Person') TempXML (Node)

For anybody interested, this is how I implemented my solution based on Tom's answer below:

CREATE PROCEDURE [dbo].[ccIU_PersonBulkImport] 
(   
    @PersonXML as xml
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE 
    @LastName AS varchar(50),
    @FirstName AS varchar(50),
    @MI AS char(1)

        DECLARE People CURSOR FORWARD_ONLY STATIC READ_ONLY FOR 
                SELECT 
                Node.value('FirstName[1]', 'varchar(50)') AS FirstName
                ,Node.value('LastName[1]', 'varchar(50)') AS LastName
                ,Node.value('MI[1]', 'char(1)') AS MI
                FROM @PersonXML.nodes('/ArrayOfPeople/Person') TempXML (Node)

        OPEN People;
        FETCH NEXT FROM People INTO @FirstName,@LastName,@MI
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC domIU_People @FirstName,@LastName,@MI -- second stored proc that inserts or updates the person
            FETCH NEXT FROM People INTO @FirstName,@LastName,@MI;
        END
    CLOSE People;
    DEALLOCATE People;

END

Answer 1

No.

You cant iterate a stored procedure like that generally they can only take objects as parameters the exception being a table object.

In this example SQL will try and call the SP and then run the select as separate events which is why you are getting the error about the missing parameter.

Your choices are to iterate through the XML and call the SP for each record, refactor the SP to either work using the XML as a separate parameter and break it down in the insert people procedure or refactor the code from the sp into the XML handling logic procedure.

Answer 2

If the stored procedure is a simple insert into the People table then you could create a new stored procedure such as:

CREATE PROCEDURE dbo.Insert_People_From_XML
    @people_xml XML
AS
BEGIN
    INSERT INTO dbo.People
    (
        id,
        first_name,
        last_name,
        middle_initial
    )
    SELECT
        Node.value('Id[1]', 'Int'),
        Node.value('FirstName[1]', 'varchar(50)'),
        Node.value('LastName[1]', 'varchar(50)'),
        Node.value('MI[1]', 'char(1)')
    FROM
        @people_xml.nodes('/ArrayOfPeople/Person') TempXML (Node)

END

If you have business logic (or other logic that you don't want to duplicate) then you may want to reuse your insert stored procedure as it is. In that case, you will have to iterate through the XML nodes. As much as I try to avoid cursors, this would be a time to use one:

DECLARE
    @id                INT,
    @first_name        VARCHAR(50),
    @last_name         VARCHAR(50),
    @middle_initial    CHAR(1)

DECLARE people_cursor CURSOR FOR
SELECT
    Node.value('Id[1]', 'Int'),
    Node.value('FirstName[1]', 'varchar(50)'),
    Node.value('LastName[1]', 'varchar(50)'),
    Node.value('MI[1]', 'char(1)')
FROM
    @people_xml.nodes('/ArrayOfPeople/Person') TempXML (Node)

OPEN people_cursor

FETCH NEXT FROM people_cursor INTO @id, @first_name, @last_name, @middle_initial

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC Your_Proc
        @id = @id,
        @first_name = @first_name,
        @last_name = @last_name,
        @middle_initial = @middle_initial

    FETCH NEXT FROM people_cursor INTO @id, @first_name, @last_name, @middle_initial
END

CLOSE people_cursor

DEALLOCATE people_cursor

NOTE: This was all written off the top of my head. I don't use XML much, so syntax may need to be corrected, you'll want to add error-handling, etc.