Import an XML Schema file and Create XML schema Collection issue

/*

In 2005 BOL, the XML schema collection is created by the following command:

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression

"The Expression is a string constant or scalar variable. Is varchar, varbinary, nvarchar, nvarbinary, or xml type". However this is not always true.

The following smaple works fine by nvarchar(max) data tyep:

*/

DECLARE @MySchemaCollection nvarchar(max)

Set @MySchemaCollection = N

<xs:schema id="MySchema" targetNamespace="http://www.me.com/my_schema&quot; xmlns:xs="http://www.w3.org/2001/XMLSchema&quot;

attributeFormDefault="unqualified" elementFormDefault="qualified">

<xs:annotation>

<xs:documentation xml:lang="en">

Data request schema for Activplant reporting system (APRS).

Copyright 2000 Activplant.com. All rights reserved.

</xs:documentation>

</xs:annotation>

<xs:element name="FileShare">

<xs:complexType>

<xs:sequence>

<xs:element name="TargetFolder" minOccurs="1" maxOccurs="1" type="xs:string" />

<xs:element name="UseSourceFileName" minOccurs="1" maxOccurs="1" type="xs:boolean" />

<xs:element name="FileName" minOccurs="1" maxOccurs="1" type="xs:string" />

<xs:element name="OverwriteExistingFile" minOccurs="1" maxOccurs="1" type="xs:boolean" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>’

CREATE

XML SCHEMA COLLECTION dbo.MyCollection AS @MySchemaCollection

GO

SELECT * FROM sys.xml_schema_collections

DROP XML SCHEMA COLLECTION dbo.MyCollection

GO

/*

If we save the schema into a file (D:\TestSchema.xsd) in ANSI code and load it into database as the following sample.

We used SINGLE_BLOB as recommended by BOL for xml.

"We recommend that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions"

*/

DECLARE

@Schema nvarchar(max)

SELECT

@Schema=CAST(BulkColumn AS nvarchar(max)) FROM OPENROWSET (BULK N‘D:\TestSchema.xsd’, SINGLE_BLOB ) A

SELECT @Schema

CREATE XML SCHEMA COLLECTION dbo.MyCollection AS @Schema

/*

You get the following error:

Msg 2378, Level 16, State 1, Line 3

Expected XML schema document.

The reason is when SQL Server imports as SINGLE_BLOB, it saves as varbinary(max). When converts the varbinary(max) to nvarchar(max), We get unmeaningful string. Since the file is in ANSI format, if we use varchar(max) or xml data type, it works fine:

*/

— Use varchar(max)

DROP

XML SCHEMA COLLECTION dbo.MyCollection

GO

DECLARE

@Schema varchar(max)

SELECT

@Schema=CAST(BulkColumn AS varchar(max)) FROM OPENROWSET (BULK N‘D:\TestSchema.xsd’, SINGLE_BLOB ) A

SELECT @Schema

CREATE XML SCHEMA COLLECTION dbo.MyCollection AS @Schema

GO

— use xml

DROP

XML SCHEMA COLLECTION dbo.MyCollection

GO

DECLARE

@Schema xml

SELECT

@Schema=CAST(BulkColumn AS xml) FROM OPENROWSET (BULK N‘D:\TestSchema.xsd’, SINGLE_BLOB ) A

SELECT @Schema

CREATE

XML SCHEMA COLLECTION dbo.MyCollection AS @Schema

/*

Since we usually not know about the code of the file, it’s safe to always use xml data type when import a schema file

*/

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s