Apr 5, 2013

Cannot create the SELECT INTO target table "" because the xml column "" is typed with a schema collection "" from database "". Xml columns cannot refer to schemata across databases.


I am creating a schema collection colData:

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)
)

Inserting a reords into it:

INSERT tblXML VALUES('
<Root>
      <Data>
            <Id>5</Id><Name>Scott</Name><Location>USA</Location>
      </Data>
      <Data>
            <Id>6</Id><Name>Greg</Name><Location>USA</Location>
      </Data>
</Root>')

Now if will execute following sql query in sql server:

SELECT * INTO #tblTemp
FROM  tblXML

We will get error message :

Cannot create the SELECT INTO target table "" because the xml column "" is typed with a schema collection "" from database "". Xml columns cannot refer to schemata across databases.

Cause: As we know the tables are create in tempdp. We cannot create table (event temp table) in other database which any columns use xml collection which has created in other database.

Solution:

We have to create the table in the current database. So instead of creating temp table we have to create actual table and insert into the recodes. For example:

SELECT * INTO tblTemp
FROM  tblXML

No comments:

Post a Comment