Ejecutar un stored procedure con XML como parametro en Sql Server

Tabla

USE [base]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Empresa](

[Id] [numeric](18, 0) NOT NULL,

[Nombre] [varchar](50) NULL,

[Direccion] [varchar](50) NULL,

 CONSTRAINT [PK_Empresa] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Stored procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE Prueba

@DatosXml XML

AS

BEGIN

SET NOCOUNT ON;

insert into Empresa (Id, Nombre, Direccion)

SELECT

  Pers.value('(Id)[1]', 'int') as 'Id',

  Pers.value('(Nombre)[1]', 'Varchar(50)') as 'Nombre',

  Pers.value('(Direccion)[1]', 'varchar(50)') as 'Direccion'

FROM

  @DatosXml.nodes('/Empresa/Persona') as EMP(Pers)

END

GO

Ejecucion

exec Prueba @DatosXml = '<Empresa> <Persona> <Id>1</Id> <Nombre>Pedro</Nombre> <Direccion>Dir 1</Direccion> </Persona> <Persona> <Id>2</Id> <Nombre>Juan</Nombre> <Direccion>Dir 2</Direccion> </Persona> </Empresa>'

Listar todos los stored procedures de una base

SELECT     sysobjects.name, syscomments.text
FROM         sysobjects INNER JOIN
                      syscomments ON sysobjects.id = syscomments.id
WHERE    
(sysobjects.type = 'P')