Error: Reference to an undefined name while creating xml schema

Question

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

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 · Answer 2

jwmott

SSC Eights!

Points: 979

June 12, 2012 at 10:41 am

#1499919

thank you.