5

writing scripts for Sql Server 2005. I am registering a schema with 

CREATE XML SCHEMA COLLECTION [dbo].[MySchema] AS N'<xsd:schema ... >'

Now, as I make changes, I would like to drop it, say with a call to

DROP XML SCHEMA COLLECTION [dbo].[MySchema]

I run this stuff fairly frequently as I am developing, like

DROP ...
CREATE ...

but this presents problems on a first-run where the Schema does not exist. I would like to do something similar to

IF OBJECT_ID ('MySchema') IS NOT NULL
    DROP ...
CREATE ...

but OBJECT_ID ('MySchema') just returns NULL. Is there a proper way to test for existence of a registered Xml Schema Collection in Sql Server 2005?

muchos gracias mis amigos :)

SteveC
  • 15,808
  • 23
  • 102
  • 173
johnny g
  • 3,533
  • 1
  • 25
  • 40

1 Answers1

13

Check sys.xml_schema_collections:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MySchema')
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    To be 100% accurate you need to check also the schema_id, since XML schema collection objects are (database) schema contained and you may have multiple collections in different schemas (dbo.MySchema, foo.MySchema, bar.MySchema etc) – Remus Rusanu Nov 13 '09 at 21:13