Red Gate forums :: View topic - XML Schema Collections
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Doc 2
SQL Doc 2 forum

XML Schema Collections

Search in SQL Doc 2 forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
Jason Hannas



Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA

PostPosted: Fri Nov 20, 2009 9:51 pm    Post subject: XML Schema Collections Reply with quote

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:
[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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Mon Nov 23, 2009 5:45 pm    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
Jason Hannas



Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA

PostPosted: Mon Nov 23, 2009 6:49 pm    Post subject: Reply with quote

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:

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).
Back to top
View user's profile Send private message
Jason Hannas



Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA

PostPosted: Mon Nov 23, 2009 6:53 pm    Post subject: Further clarification Reply with quote

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

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

    SELECT @PaymentXML

GO


although I still get the goofy [RG::XMLSC:::].[cms].[xsdPayment] thing.
Back to top
View user's profile Send private message
Jason Hannas



Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA

PostPosted: Mon Nov 23, 2009 6:57 pm    Post subject: Justification for poor code Reply with quote

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. Mad
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group