Select the product you need help with

You receive a NULL value when you use XQuery to retrieve the value of an XML element in SQL Server 2005

Article ID: 914638 - View products that this article applies to.

Bug #: 20011171 (SQLBUDT)

SYMPTOMS

You receive a NULL value when use XQuery to retrieve the value of an XML element when the following conditions are true:

The XML element is declared as a complex type that has simple content in the associated schema definition.
The value of the mixed attribute of the XML element declaration in the associated schema definition is true.
You use the data function to retrieve the value of the XML element.

Back to the top | Give Feedback

This problem occurs because the value of an XML element is processed as an inline value to the XML element when the XML element is declared as a complex type that has simple content and mixed content. Therefore, the data function cannot retrieve the typed value of the XML element. Typically, the value of the XML element is processed as the typed value of the child of the XML element.

Back to the top | Give Feedback

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089

(http://support.microsoft.com/kb/913089/ )

How to obtain the latest service pack for SQL Server 2005

Back to the top | Give Feedback

WORKAROUND

To work around this problem, follow these steps:

You must change the table column that has its data entry validated against the problematic XML schema collection. This table column must be changed from the typed XML column to the untyped XML column. To do this, use a Transact-SQL statement that is similar to the following:
ALTER TABLE <TableName> ALTER COLUMN <ColumnName> XML GO
Drop the existing XML schema collection. Then, create a new XML schema collection that has the same definition as the old XML schema, except that the value of the mixed attribute of the XML element declaration is set to false. To do this, use a Transact-SQL statement that is similar to the following:
DROP XML SCHEMA COLLECTION <OldSchemaName> GO CREATE XML SCHEMA COLLECTION <NewSchemaName> AS N’<NewSchemaDefinition>’ GO
Set the table column back to the typed XML column that validates against the new XML schema collection. To do this, use a Transact-SQL statement that is similar to the following:
ALTER TABLE <TableName> ALTER COLUMN <ColumnName> XML(<NewSchemaName>) GO

Note <TableName> represents the name of the table. <ColumnName> represents the name of the XML table column. <OldSchemaName> represents the name of the old problematic schema collection. <NewSchemaName> represents the name of the new schema collection. <NewSchemaDefinition> represents the code that defines the new schema collection.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft SQL Server 2005 Service Pack 1.

Back to the top | Give Feedback

MORE INFORMATION

For a complex type that has simple content, we do not recommend that you set the value of the mixed attribute of the complex type to true. The specification on the XML schema structures that is available on the World Wide Web Consortium (W3C) Web site also discusses this issue. For more information, see the "3.4.3" section and the "3.4.4" section in the following Web site:

http://www.w3.org/TR/xmlschema-1/#Complex_Type_Definitions

(http://www.w3.org/TR/xmlschema-1/#Complex_Type_Definitions)

Steps to reproduce the problem

Open SQL Server 2005 Management Studio.
Connect to an instance of SQL Server.
Expand Databases and then click a database that you want to modify.
On the File menu, point to New, and then click Database Engine Query. The Query Editor window appears.

Paste the following code example in the Query Editor window:

create xml schema collection myschema as N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="ElementName" type="xs:integer" />
		
	<xs:complexType name="newct" mixed="true">
		<xs:simpleContent>
			<xs:extension base="xs:integer">
				<xs:attribute name="bar" type="xs:integer" />
			</xs:extension>
		</xs:simpleContent>
	</xs:complexType>
</xs:schema>'
go

create table xml_test (pk int primary key identity(1,1), xmlcol xml(myschema))
go

insert xml_test values ('
<ElementName xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="newct" bar="10">30</ElementName>')
go

insert xml_test values ('
<ElementName>30</ElementName>')
go

select xmlcol.query('data(/ElementName)')
from xml_test

The output is similar to the following: