XML Schema Collections

• 
  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.

  Brian Donahue November 23, 2009 11:45

  - 0 +
• 
  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 November 23, 2009 12:49

  - 0 +
• 
  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 November 23, 2009 12:53

  - 0 +
• 
  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 November 23, 2009 12:57

  - 0 +

Add comment

Please sign in to leave a comment.