Honestly, I think the simplest solution is XML::XPath.

my $xp = XML::XPath->new( ioref => $input );
my $mp = $xp->find('/meta-parameter');
for my $n ($mp->get_nodelist) {
    if( $xp->findvalue('@type' => $n) eq "stored-procedure" ) {
        warn "found: " . $xp->findvalue( '@id' => $n );
    }
}
[download]

For regulars of this type, I usually multi-stage it so I can be sure I matched the right thing.

if( m/<([^>]*meta-parameter[^>]*stored-procedure[^>]*)>/ ) {
    my $tag = $1;
    if( $tag =~ m/id=['"](.+?)['"]/ ) {
        my $id = $1;
        # argueably not the most robust thing in the whole wide world,
        # but you get the idea
    }
}
[download]