XML Schema Collections

Jason Hannas

I've finally gotten around to upgrading my SQL Doc 1 to the latest version (with some private build help from Linda and Matthew--thanks!). However, I was a little disappointed to see that XML Schema Collections are still not handled well in the dependencies.

Stored procedure documentation shows the XML schema collection, but doesn't link to it as shown below:

[RG::XMLSC:::].[cms].[xsdPayment]

Conversely, XML Schema Collection documentation doesn't show anything in the "Used By" section. This seems like a glaring omission that I'm hoping can/will be correct in an imminent release.

Brian Donahue

Hi Jason,

Thanks for your post. Either I don't understand the problem correctly or haven't gone through the same steps as you have, but I cannot reproduce the issue.

I have the following schema collection:

CREATE XML SCHEMA COLLECTION [dbo].[SQLCompareReport] 
AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" ...

CREATE TABLE [dbo].[SQLCompareReports]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[filename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[report] [xml] (CONTENT [dbo].[SQLCompareReport]) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

In the documentation for the XML Schema Collection "SQLCompareReport", the "used by" section reports [dbo].[SQLCompareReports].

If you have some script that will reproduce the issue, or if maybe the database is actually operating in compatibility mode 80, please let me know.

Jason Hannas

HI Brian,

Yes, let me clarify. It's not the tables, but the stored procedures. I haven't tested functions or views. For instance, I have a stored procedure that takes input from a webservice and validates against the XML Schema Collection as such:

CREATE XML SCHEMA COLLECTION [cms].[xsdPayment] 
AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">...


CREATE PROC cms.pRecordPayment
    @PaymentXML nvarchar(max)
AS
SET NOCOUNT ON;
    
    DECLARE @DocHandle int, @myXML xml;
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @PaymentXML;
    SET @myXML = CAST(@PaymentXML AS XML(cms.xsdPayment)) ;

...

This behavior has been the same on SQL Server 2005 (comp. 80 and 90) and on SQL Server 2008 (comp. 90 and 100).

Jason Hannas

It does work correctly if the parameter is bound to the schema collection in the definition, such as:

CREATE PROC cms.pRecordPayment2
    @PaymentXML xml(cms.xsdPayment)
AS

    SELECT @PaymentXML

GO

although I still get the goofy [RG::XMLSC:::].[cms].[xsdPayment] thing.

Jason Hannas

And before anyone asks why I'm not simply coding my stored procedures correctly (as in #2), the unfortunate reality is that these procs are front-ended by ColdFusion Server, which throws up if we try to pass XML to the database as an actual XML parameter. Therefore, I must receive it as text and convert it to a real XML object. :x