How to use openxml within a user defined function in SQL Server

Question

I have an XML structure that I parse using OPENXML within a stored procedure to retrieve parameters used to perform a query. This procedure was a base procedure that a different stored procedure (procedure 2) is calling. Procedure 2 uses an insert-exec construct to get the data from the base procedure. This works great as long as we only call Procedure 2 or the base procedure.

My first problem is that I have a different procedure (procedure 3) that now needs to get the result from procedure 2 (I need the business rules that this procedure enforces), but cannot due to the message:

An INSERT EXEC statement cannot be nested.

I then tried to take the base procedure and make it a table valued function, but when I execute it, I receive the message:

Only functions and some extended stored procedures can be executed from within a function.

How do I get around one or both of these issues?

EDIT 1 I am including a code snippet to show the base procedure (Procedure 1) and the procedure implementing business requirements on the results of that procedure (Procedure 2). If there is a 3rd procedure that needs the results with the business rules applied, we run into problems.

create procedure dbo.p_Proc
  @Xml xml
as
begin
  set nocount on;

  declare @l_idoc int
      , @InfoId int
      , @InfoTypeId int
      , @Id int
      , @Name varchar(50)
      , @StatusId int
      , @RoleId int
      , @XmlBase xml
      , @l_path varchar(100);

  declare @T_TABLE table(
        InfoId int
      , InfoTypeId int
  );

  declare @T_RESULT table
  (
      Field1 int
    , Field2 varchar(50)
    , Field3 int
  );

  EXEC sp_xml_preparedocument @l_idoc OUTPUT, @Xml;

  set @l_path = '/xml/Info';        
  insert into @T_TABLE(InfoId, InfoTypeId)
  select InfoId, InfoTypeId
  from  OPENXML (@l_idoc, @l_path, 1)
      with (
         InfoId int './@InfoId'
         , InfoTypeId int './@InfoTypeId'
      );

  select @InfoId = InfoId
     , @InfoTypeId = InfoTypeId
    from @T_TABLE;

  -- create the XML to call the base widgets
  select @XmlBase = 
  (
    select *
      from
    (
      select t.Id, t.Name, t.StatusId, t.RoleId
        from @T_TABLE w
      inner join dbo.T_TABLE2 t
        on t.InfoId = w.InfoId
         and t.InfoTypeId = w.InfoTypeId
    ) b
    for xml raw('Widget'), root('Xml')
  );

  -- retrieve widgets from base security
  insert into @T_RESULT(Field1, Field2, Field3)
  exec dbo.p_ProcBase @Xml = @XmlBase;

  -- apply business logic here


  select w.Field1, w.Field2, w.Field3
    from @T_RESULT w;
end;
go

create procedure dbo.p_ProcBase
  @Xml xml = null
as
begin
  set nocount on;

  declare @l_idoc int
      , @Id int
      , @Name varchar(50)
      , @StatusId int
      , @RoleId int
      , @l_path varchar(100);

  declare @T_Table table(
        Id int
      , Name varchar(50)
      , StatusId int
      , RoleId int
  );

  EXEC sp_xml_preparedocument @l_idoc OUTPUT, @Xml;

  set @l_path = '/Xml/Widget';      
  insert into @T_Table(Id, Name, StatusId, RoleId)
  select Id, Name, StatusId, RoleId
  from  OPENXML (@l_idoc, @l_path, 1)
      with (
         ProjectId int './@Id'
         , WidgetTypeName varchar(50) './@Name'
         , WorkflowStatusId int './@StatusId'
         , UserRoleId bigint './@RoleId'
      );

  select @Id = w.Id
     , @Name = w.Name
     , @StatusId = w.StatusId
     , @RoleId = w.RoleId
    from @T_Table w;

  -- retrieve enabled widgets for which the user has a role in the current workflow state
  select t.Field1, t.Field2, t.Field3
    from dbo.T_TABLE t
   where t.StatusId = @StatusId
     and t.RoleId = @RoleId;
end;

Answer 1

In order to send a data set (table) between procs, you must use a Table type, store the output of proc2 in a variable of table type and add a readonly only table type parameter to proc3

First you must create a table type to map your output from proc2:

CREATE TYPE T_RESULT AS TABLE
(
      Field1 int
    , Field2 varchar(50)
    , Field3 int
  );

In dbo.p_Proc change @T_RESULT to:

declare @T_RESULT T_RESULT

Then create proc3:

CREATE PROCEDURE dbo.proc3
    @T_RESULT T_RESULT READONLY
AS 
BEGIN    
    SET NOCOUNT ON
    INSERT INTO T3(...) 
    SELECT ... FROM @T_RESULT 
END

Don't forget to add READONLY after a table type parameter in a proc.