Type XML

I have the following tsql:

-- TSQL START
DECLARE @XSD varchar(MAX)
SET @XSD = '<?xml version="1.0" encoding="utf-8" ?>
<xs:schema elementFormDefault="qualified" targetNamespace="http://a"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Widget">
<xs:complexType>
<xs:all>
<xs:element name="var1" minOccurs="0" maxOccurs="1"
type="xs:short" />
</xs:all>
</xs:complexType>
</xs:element>
</xs:schema>'

CREATE XML SCHEMA COLLECTION [Test_XMLText_Schema] AS @XSD
GO

DECLARE @xmlUnTyped xml
DECLARE @xmlTyped xml (DOCUMENT Test_XMLText_Schema)

SET @xmlUnTyped = '<?xml version="1.0" encoding="utf-8"?>
<Widget xmlns="http://a">
<var1/>
</Widget>'


BEGIN TRY
SET @xmlTyped = @xmlUnTyped
SELECT @xmlTyped
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH

DROP XML SCHEMA COLLECTION [dbo].[Test_XMLText_Schema]

-- TSQL END


It loads the XML fine into the untyped XML variable. Trying to load it
into the typed XML variable I receive the following error:
XML Validation: Invalid simple type value: ''. Location: /*:Widget[1]/
*:var1[1]

Adding nillable="true" to var1 XSD declaration does not help.
Changing the type of var1 element to xs:string allows it to work but I
don't want to do that if I don't have to.

Ithink I'm just missing something really simple.

Thanks in advance,

Kevin

The way you've defined the XML schema you have two choices:
1. "var1" must be of type xs:short
2. "var1" can be missing

If add add nillable=true to the schema definition of var1, this also works:
SET @xmlTyped = '<?xml version="1.0" encoding="utf-8"?>
<Widget xmlns="http://a"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<var1 xsi:nil="true"/>
</Widget>'

By using <var1/> in a document, you are saying that var1's content is an
empty string. Which also works if you type var1 as a string (but not as an
xs:short).

Hope this helps,
Cheers,
Bob Beauchemin
SQLskills

"kpb" wrote in message

>I have the following tsql:
>
> -- TSQL START
> DECLARE @XSD varchar(MAX)
> SET @XSD = '<?xml version="1.0" encoding="utf-8" ?>
> <xs:schema elementFormDefault="qualified" targetNamespace="http://a"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Widget">
> <xs:complexType>
> <xs:all>
> <xs:element name="var1" minOccurs="0" maxOccurs="1"
> type="xs:short" />
> </xs:all>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
>
> CREATE XML SCHEMA COLLECTION [Test_XMLText_Schema] AS @XSD
> GO
>
> DECLARE @xmlUnTyped xml
> DECLARE @xmlTyped xml (DOCUMENT Test_XMLText_Schema)
>
> SET @xmlUnTyped = '<?xml version="1.0" encoding="utf-8"?>
> <Widget xmlns="http://a">
> <var1/>
> </Widget>'
>
>
> BEGIN TRY
> SET @xmlTyped = @xmlUnTyped
> SELECT @xmlTyped
> END TRY
> BEGIN CATCH
> PRINT ERROR_MESSAGE()
> END CATCH
>
> DROP XML SCHEMA COLLECTION [dbo].[Test_XMLText_Schema]
>
> -- TSQL END
>
>
> It loads the XML fine into the untyped XML variable. Trying to load it
> into the typed XML variable I receive the following error:
> XML Validation: Invalid simple type value: ''. Location: /*:Widget[1]/
> *:var1[1]
>
> Adding nillable="true" to var1 XSD declaration does not help.
> Changing the type of var1 element to xs:string allows it to work but I
> don't want to do that if I don't have to.
>
> Ithink I'm just missing something really simple.
>
> Thanks in advance,
>
> Kevin