Error: Reference to an undefined name while creating xml schema

• jwmott

  SSC Eights!

  Points: 979

  More actions

  June 11, 2012 at 11:48 am

  #392483

  trying to design a schema for a project where we will be exchanging data with another organization. The initial problem I was having is how to allow a numeric field to be nillable (see element honePhone). After doing some research I found an article suggesting that I create my own custom simple types and now those types are throwing the following error

  Msg 2307, Level 16, State 1, Line 3

  Reference to an undefined name 'emptystring'

  -- DROP the previous SCHEMA COLLECTION

  IF EXISTS(

  SELECT * FROM sys.xml_schema_collections

  WHERE name = 'Lab1'

  ) BEGIN

  DROP XML SCHEMA COLLECTION Lab1

  END

  GO

  -- CREATE the SCHEMA COLLECTION with the updated

  -- definition.

  CREATE XML SCHEMA COLLECTION Lab1 AS

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

  <xsd:simpleType name="emptystring">

  <xsd:restriction base="xsd:string">

  <xsd:enumeration value="" />

  </xsd:restriction>

  </xsd:simpleType>

  <xsd:simpleType name="emptyorint">

  <xsd:union memberTypes="emptystring xsd:int" />

  </xsd:simpleType>

  <xsd:element name="Diplomate">

  <xsd:complexType>

  <xsd:sequence>

  <xsd:elementname="ACR_ID"

  type="xsd:string"/>

  <xsd:elementname="ABR_ID"

  type="xsd:int"

  minOccurs="1"/>

  <xsd:elementname="lastName"

  type="xsd:string"/>

  <xsd:elementname="firstName"

  type="xsd:string"/>

  <xsd:elementname="middleInitial"

  type="xsd:string"/>

  <xsd:elementname="address1"

  type="xsd:string"/>

  <xsd:elementname="address2"

  type="xsd:string"/>

  <xsd:elementname="address3"

  type="xsd:string"/>

  <xsd:elementname="city"

  type="xsd:string"/>

  <xsd:elementname="state"

  type="xsd:string"/>

  <xsd:elementname="postalCode"

  type="xsd:string"/>

  <xsd:elementname="homePhone"

  type="emptyorint"

  nillable="true"/>

  <xsd:elementname="workPhone"

  type="xsd:int"/>

  <xsd:elementname="cellPhone"

  type="xsd:integer"/>

  <xsd:elementname="workEmail"

  type="xsd:string"/>

  <xsd:elementname="homeEmail"

  type="xsd:string"/>

  <xsd:elementname="takerStatus"

  type="xsd:int"/>

  <xsd:elementname="examinationDate"

  type="xsd:int"/>

  <xsd:elementname="disability"

  type="xsd:int"/>

  <xsd:elementname="examVendorID"

  type="xsd:int"/>

  <xsd:elementname="validLicense"

  type="xsd:int"/>

  <xsd:elementname="performedExaminations"

  type="xsd:int"/>

  <xsd:elementname="acquainted"

  type="xsd:int"/>

  <xsd:elementname="amaPraCategory1"

  type="xsd:int"/>

  <xsd:elementname="examResult"

  type="xsd:int"/>

  </xsd:sequence>

  </xsd:complexType>

  </xsd:element>

  </xsd:schema>'

  GO

  DECLARE @x XML(Lab1)

  SET @x =

  '

  <xsi:Diplomate xmlns:xsi="myNS">

  <ACR_ID></ACR_ID>

  <ABR_ID>1</ABR_ID>

  <lastName></lastName>

  <firstName></firstName>

  <middleInitial></middleInitial>

  <address1></address1>

  <address2></address2>

  <address3></address3>

  <city></city>

  <state></state>

  <postalCode></postalCode>

  <homePhone xsi:nil="true"/>

  <workPhone></workPhone>

  <cellPhone></cellPhone>

  <workEmail></workEmail>

  <homeEmail></homeEmail>

  <takerStatus></takerStatus>

  <examinationDate></examinationDate>

  <disability></disability>

  <examVendorID></examVendorID>

  <validLicense></validLicense>

  <performedExaminations></performedExaminations>

  <acquainted></acquainted>

  <amaPraCategory1></amaPraCategory1>

  <examResult></examResult>

  </xsi:Diplomate>

  '

  select @x as wakka

• okbangas

  SSChampion

  Points: 11773

  More actions

  June 12, 2012 at 6:43 am

  #1499753

  jwmott (6/11/2012)

  ---

  trying to design a schema for a project where we will be exchanging data with another organization. The initial problem I was having is how to allow a numeric field to be nillable (see element honePhone). After doing some research I found an article suggesting that I create my own custom simple types and now those types are throwing the following error

  Msg 2307, Level 16, State 1, Line 3

  Reference to an undefined name 'emptystring'

  You have forgotten the namespace

  -- DROP the previous SCHEMA COLLECTION

  IF EXISTS(

  SELECT * FROM sys.xml_schema_collections

  WHERE name = 'Lab1'

  ) BEGIN

  DROP XML SCHEMA COLLECTION Lab1

  END

  GO

  -- CREATE the SCHEMA COLLECTION with the updated

  -- definition.

  CREATE XML SCHEMA COLLECTION Lab1 AS

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

  <xsd:simpleType name="emptystring">

  <xsd:restriction base="xsd:string">

  <xsd:enumeration value="" />

  </xsd:restriction>

  </xsd:simpleType>

  <xsd:simpleType name="emptyorint">

  <xsd:union memberTypes="ns:emptystring xsd:int" />

  </xsd:simpleType>

  <xsd:element name="Diplomate">

  <xsd:complexType>

  <xsd:sequence>

  <xsd:elementname="ACR_ID"

  type="xsd:string"/>

  <xsd:elementname="ABR_ID"

  type="xsd:int"

  minOccurs="1"/>

  <xsd:elementname="lastName"

  type="xsd:string"/>

  <xsd:elementname="firstName"

  type="xsd:string"/>

  <xsd:elementname="middleInitial"

  type="xsd:string"/>

  <xsd:elementname="address1"

  type="xsd:string"/>

  <xsd:elementname="address2"

  type="xsd:string"/>

  <xsd:elementname="address3"

  type="xsd:string"/>

  <xsd:elementname="city"

  type="xsd:string"/>

  <xsd:elementname="state"

  type="xsd:string"/>

  <xsd:elementname="postalCode"

  type="xsd:string"/>

  <xsd:elementname="homePhone"

  type="ns:emptyorint"

  nillable="true"/>

  <xsd:elementname="workPhone"

  type="xsd:int"/>

  <xsd:elementname="cellPhone"

  type="xsd:integer"/>

  <xsd:elementname="workEmail"

  type="xsd:string"/>

  <xsd:elementname="homeEmail"

  type="xsd:string"/>

  <xsd:elementname="takerStatus"

  type="xsd:int"/>

  <xsd:elementname="examinationDate"

  type="xsd:int"/>

  <xsd:elementname="disability"

  type="xsd:int"/>

  <xsd:elementname="examVendorID"

  type="xsd:int"/>

  <xsd:elementname="validLicense"

  type="xsd:int"/>

  <xsd:elementname="performedExaminations"

  type="xsd:int"/>

  <xsd:elementname="acquainted"

  type="xsd:int"/>

  <xsd:elementname="amaPraCategory1"

  type="xsd:int"/>

  <xsd:elementname="examResult"

  type="xsd:int"/>

  </xsd:sequence>

  </xsd:complexType>

  </xsd:element>

  </xsd:schema>'

  GO

  DECLARE @x XML(Lab1)

  SET @x =

  '

  <xsi:Diplomate xmlns:xsi="myNS">

  <ACR_ID></ACR_ID>

  <ABR_ID>1</ABR_ID>

  <lastName></lastName>

  <firstName></firstName>

  <middleInitial></middleInitial>

  <address1></address1>

  <address2></address2>

  <address3></address3>

  <city></city>

  <state></state>

  <postalCode></postalCode>

  <homePhone xsi:nil="true"/>

  <workPhone></workPhone>

  <cellPhone></cellPhone>

  <workEmail></workEmail>

  <homeEmail></homeEmail>

  <takerStatus></takerStatus>

  <examinationDate></examinationDate>

  <disability></disability>

  <examVendorID></examVendorID>

  <validLicense></validLicense>

  <performedExaminations></performedExaminations>

  <acquainted></acquainted>

  <amaPraCategory1></amaPraCategory1>

  <examResult></examResult>

  </xsi:Diplomate>

  '

  select @x as wakka

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]

• jwmott

  SSC Eights!

  Points: 979

  More actions

  June 12, 2012 at 10:41 am

  #1499919

  thank you.