error: Reference to an undefined name when creating schema

Question

error: Reference to an undefined name when creating schema

jwmott

SSC Eights!

Points: 979
More actions
June 11, 2012 at 12:14 pm

#100845

trying to create a schema (sql 2008 R2) as shown but get the error
Msg 2307, Level 16, State 1, Line 3
Reference to an undefined name 'emptystring'
Trying to create a schema to share with another organization to facilitate data exchange between the two organizations.
-- 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 4 posts - 1 through 3 (of 3 total)

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

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 1

I think the problem is around the namespaces. I have updated the code to the following and i think it works how you need it to:

-- 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: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:simpleType name="emptyorint">

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

<xsd:pattern value="[0-9]*"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:schema>'

GO

DECLARE @x XML(Lab1)

SET @x =

'

<ns:Diplomate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="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>1</workPhone>

<cellPhone>1</cellPhone>

<workEmail></workEmail>

<homeEmail></homeEmail>

<takerStatus>1</takerStatus>

<examinationDate>1</examinationDate>

<disability>1</disability>

<examVendorID>1</examVendorID>

<validLicense>1</validLicense>

<performedExaminations>1</performedExaminations>

<acquainted>1</acquainted>

<amaPraCategory1>1</amaPraCategory1>

<examResult>1</examResult>

</ns:Diplomate>

'

select @x as wakka

The changes that i made were:

1. prefixed type on homePhone to: type="ns:emptyorint"

2. changed simpleType to use regular expression to validate numbers only

3. changed namespaces on xml example

This seems to allow nil values and only numbers in the homePhone node.

Hope this helps or gets you closer.

jwmott SSC Eights! Points: 979 More actions · Answer 2

excellent. thank you. so it appears that I should use type string and validate using regular expression as I just can't get non string data types to allow nulls.

Thanks again, I've been trying to get this for two days.

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 3

jwmott (6/12/2012)
excellent. thank you. so it appears that I should use type string and validate using regular expression as I just can't get non string data types to allow nulls.
Thanks again, I've been trying to get this for two days.

No problem.

The main issue was around the namespaces and not the data type. I used the string type with a reg ex in my example because of habit with working with phone number type data and the non-numeric characters they invariably contain. 🙂

The following code uses the xsd:int type instead:

-- 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: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="xsd:integer"

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 =

'

<ns:Diplomate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="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>1</workPhone>

<cellPhone>1</cellPhone>

<workEmail></workEmail>

<homeEmail></homeEmail>

<takerStatus>1</takerStatus>

<examinationDate>1</examinationDate>

<disability>1</disability>

<examVendorID>1</examVendorID>

<validLicense>1</validLicense>

<performedExaminations>1</performedExaminations>

<acquainted>1</acquainted>

<amaPraCategory1>1</amaPraCategory1>

<examResult>1</examResult>

</ns:Diplomate>

'

select @x as wakka