| Author |
Message |
Jason Hannas
Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA
|
 Posted: Fri Nov 20, 2009 9:51 pm Post subject: XML Schema Collections |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6348
Location: Red Gate Software
|
| Posted: Mon Nov 23, 2009 5:45 pm Post subject: |
|
|
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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Jason Hannas
Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA
|
| Posted: Mon Nov 23, 2009 6:49 pm Post subject: |
|
|
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 |
|
|
Jason Hannas
Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA
|
| Posted: Mon Nov 23, 2009 6:53 pm Post subject: Further clarification |
|
|
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 |
|
|
Jason Hannas
Joined: 15 Dec 2006
Posts: 23
Location: Aberdeen, MD USA
|
| Posted: Mon Nov 23, 2009 6:57 pm Post subject: Justification for poor code |
|
|
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.  |
|
| Back to top |
|
|
|
