To Mr. Lifesaver Bob,

Yes, I made it work with your xsd schema. But you successfully ignored my
questions, so that I can understand why there are such problems.
You underestimate the number of changes, but there are at least...

As far as I can tell, the only differences required to make this work are:
1) add sql:is-constant with namespace
2) convert xs:integers to xs:decimals or even xs:string
3) convert xs:date to xs:string

I haven't been ignoring your questions, I'm afraid I can't answer questions
as to why xsds won't work with SQL Server. Maybe they are meant to be
compatible with .net. Have you tried any .net methods of reading XML files
with schemas? Maybe there are compatibility problems with XML data-types and
SQL Server data-types. It's not unusual in ETL to have to load to a staging
table (eg where all columns are VARCHAR) and then clean the data.

All I know is, these xsd are not quite suitable for SQL Server straightaway,
but need a few minor modifications.

I have been working on way to make this easy, so it can be used against
multiple xsds. Try this:

IF OBJECT_ID( 'dbo.usp_fix_xsd' ) IS NOT NULL
DROP PROC dbo.usp_fix_xsd
GO

How did you get on with this stored procedure? Did it work?

I noticed it wasn't quite doing the namespace correctly but don't know if
that caused a problem for you.

"Travis McGee" wrote: