I have an AdventureWorks database that I created a snapShot
(AdventureWorksSP).  I deleted several ContactIDs from the Person.Contact
table.  I drop the Person.Contact table from the AdvenureWorks database.  I
would like to use a SELECT * INTO Person.Contact From AdventureWorksSP to
create the Person.Contact table and copy the data from the SnapShot but I
received the following error:



Error:
Cannot create the SELECT INTO target table "Person.Contact" because the xml
column "AdditionalContactInfo" is typed with a schema collection
"AdditionalContactInfoSchemaCollection" from database "AdventureWorksSP" xml
column cannot refer to schema across database.

Please help me resolve this error.

Thanks,

Show quote Hide quote Hi Joe


If you scripted the Person.Contact table you would see that the XML column
AdditionalContactInfo is typed XML with the schema
AdditionalContactInfoSchemaCollection i.e

[AdditionalContactInfo] [xml](CONTENT
[Person].[AdditionalContactInfoSchemaCollection]) NULL,


select *
from sys.xml_schema_collections

returns as a row for AdditionalContactInfoSchemaCollection

and

SELECT
xml_schema_namespace(N'Person',N'AdditionalContactInfoSchemaCollection')
GO
shows what it is.

To get around the SCHEMA You can cast your typed XML to untyped XML for the
created table such as

SELECT ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix,
EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt,
CAST(AdditionalContactInfo AS XML) AS AdditionalContactInfo, rowguid,
ModifiedDate
INTO tempdb.dbo.tmpcontacts
FROM [Person].[Contact]

If you want to make the XML column typed again you can then create a schema
collection and alter the table


USE TEMPDB
GO

CREATE XML SCHEMA COLLECTION AdditionalContactInfoSchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
.... this is what SELECT
xml_schema_namespace(N'Person',N'AdditionalContactInfoSchemaCollection')
gave!!
</xsd:schema>' ;
GO


ALTER TABLE tempdb.dbo.tmpcontacts
ALTER COLUMN  [AdditionalContactInfo] [xml](CONTENT
[AdditionalContactInfoSchemaCollection]) NULL

John

Trigger and Misc.
cmdshell how to run server side vbscript
Issue with subselect
HTTP T-SQL queries (with XML support) don't work on sql server 200
Adding memory has degraded performance
Retaining same Instance Name in side-by-side upgrade/migration
free text search in large starschemes
Bitwise in SQL Server
SQL Server 2005 SSMS List Role Permission
[SAN] More spindles via multiple RAID/LUNs, or fewer & more focused LUNs?