Use Of Xml Schema In Sproc

It appears to be impossible to create an
xml schema in a stored procedure and then use it in a temporary table in the
stored procedure. Did anyone else experience this? I am using this stored
procedure:



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[validate_data]

@variable_uid int,

@xml_schema xml,

@flag int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;



declare @schema xml



set @flag = 0



create xml schema collection dbo.xsch AS @xml_schema



CREATE TABLE
temp_row_values

(

row_value_uid
int,

xml_fragment xml (dbo.xsch) not null

)



drop xml schema collection dbo.xsch

drop table
temp_row_values



END



It produces this error message:



Msg 6314, Level 16, State 1, Procedure validate_data, Line 22

Collection specified does not exist in metadata : 'dbo.xsch'



If I execute this query:



exec validate_data 120, '<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element
name="int_ma">
<xs:annotation>
<xs:documentation xml:lang="en" /> </xs:annotation> <xs:complexType> <xs:all> <xs:element
minOccurs="1" maxOccurs="1" name="value"> <xs:simpleType> <xs:union> <xs:simpleType> <xs:restriction
base="xs:integer">
<xs:minInclusive value="1" /> <xs:maxInclusive
value="100" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType>
<xs:restriction base="xs:string"> <xs:enumeration
value="" /> <xs:enumeration value="none"
/>
</xs:restriction>
</xs:simpleType>
</xs:union>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="1" maxOccurs="1"
name="unit">
<xs:simpleType>
<xs:restriction base="xs:string"> <xs:enumeration
value="mm" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="1"
name="comment"> <xs:simpleType> <xs:restriction
base="xs:string" />
</xs:simpleType>
</xs:element>
</xs:all>
</xs:complexType>
</xs:element>
</xs:schema>', 1



Any help to €˜make it possible€™ would
be very much apprecaited. Many thanks.



Chris