Apr 5, 2013

Views and inline functions cannot return xml columns that are typed with a schema collection registered in a database other than current. Column "" is typed with the schema collection "", which is registered in database "".


I am creating a schema collection colData in the ExactHelp database

USE ExactHelp

CREATE XML SCHEMA COLLECTION colData
AS N'<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Data">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="Id" type="xs:unsignedByte" />
              <xs:element minOccurs="0" name="Name" type="xs:string" />
              <xs:element minOccurs="0" name="Location" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Now we a creating the a table tblXml which has a column of data type XML and use colData schema collection.

CREATE TABLE  tblXML(
      XML_ID BIGINT IDENTITY,
      Data XML (colData)
)

Now if will create following view or inline function in master database:

USE MASTER
Go

CREATE VIEW vwXML
AS
      SELECT TOP(1) * FROM  ExactHelp.dbo.tblXML

Or

USE MASTER
Go

CREATE FUNCTION fnXML()
RETURNS TABLE
AS
RETURN(
      SELECT TOP(1) * FROM  ExactHelp.dbo.tblXML
)

We will get error message :

Views and inline functions cannot return xml columns that are typed with a schema collection registered in a database other than current. Column "" is typed with the schema collection "", which is registered in database "".

Cause: It is necessary to create a view or an inline function is same database if it use table which use schema collection, in which database schema collection has created. For example:

USE ExactHelp
Go

CREATE VIEW vwXML
AS
      SELECT TOP(1) * FROM  tblXML
     
Or

USE ExactHelp
Go

CREATE FUNCTION fnXML()
RETURNS TABLE
AS
RETURN(
      SELECT TOP(1) * FROM  tblXML
)

No comments:

Post a Comment