XML Schema Collections

Documents SQL Server 2005 and 2008 databases.

Moderators: JonathanWatts, David Atkinson, Michelle Taylor

XML Schema Collections

Postby Jason Hannas » Fri Nov 20, 2009 9:51 pm

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:

Code: Select all
[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.
Jason Hannas
 
Posts: 23
Joined: Fri Dec 15, 2006 4:57 pm
Location: Aberdeen, MD USA

Postby Brian Donahue » Mon Nov 23, 2009 5:45 pm

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:
Code: Select all
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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Jason Hannas » Mon Nov 23, 2009 6:49 pm

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:

Code: Select all
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
 
Posts: 23
Joined: Fri Dec 15, 2006 4:57 pm
Location: Aberdeen, MD USA

Further clarification

Postby Jason Hannas » Mon Nov 23, 2009 6:53 pm

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

Code: Select all
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
 
Posts: 23
Joined: Fri Dec 15, 2006 4:57 pm
Location: Aberdeen, MD USA

Justification for poor code

Postby Jason Hannas » Mon Nov 23, 2009 6:57 pm

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
Jason Hannas
 
Posts: 23
Joined: Fri Dec 15, 2006 4:57 pm
Location: Aberdeen, MD USA


Return to SQL Doc 2

Who is online

Users browsing this forum: No registered users and 0 guests