XML strange problem

• Resender

  SSCertifiable

  Points: 7443

  More actions

  January 10, 2013 at 7:02 am

  #269180

  Ok I'm doing a few exercises in SQL Server 2012

  And I did the following

  IF OBJECT_ID('ClientInfoCollection') IS NOT NULL

  DROP XML SCHEMA COLLECTION ClientInfoCollection;

  GO

  CREATE XML SCHEMA COLLECTION ClientInfoCollection AS

  '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="urn:ClientInfoNamespace"

  targetNamespace="urn:ClientInfoNamespace"

  elementFormDefault="qualified">

  <xsd:element name="People">

  <xsd:complexType>

  <xsd:sequence>

  <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">

  <xsd:complexType>

  <xsd:sequence>

  <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />

  <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />

  <xsd:element name="FavoriteBooks" minOccurs="0" maxOccurs="1">

  <xsd:complexType>

  <xsd:sequence>

  <xsd:element name="Book" type="xsd:string" minOccurs="0" maxOccurs="5" />

  </xsd:sequence>

  </xsd:complexType>

  </xsd:element>

  </xsd:sequence>

  <xsd:attribute name="id" type="xsd:integer" use="required"/>

  </xsd:complexType>

  </xsd:element>

  </xsd:sequence>

  </xsd:complexType>

  </xsd:element>

  </xsd:schema>';

  GO

  And I get the following message

  Msg 6348, Level 16, State 1, Line 2

  Specified collection 'ClientInfoCollection' cannot be created because it already exists or you do not have permission.

  I don't get it,why am I getting this error

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 10, 2013 at 7:54 am

  #1575815

  Change

  IF OBJECT_ID('ClientInfoCollection') IS NOT NULL

  to

  IF EXISTS(SELECT * FROM sys.xml_schema_collections WHERE name='ClientInfoCollection')

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Resender

  SSCertifiable

  Points: 7443

  More actions

  January 15, 2013 at 8:36 am

  #1577370

  That worked