Microsoft KB Archive/914638

Knowledge Base

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

Article ID: 914638

Article Last Modified on 4/6/2006

---

APPLIES TO

• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition

---

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.

CAUSE

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.

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 How to obtain the latest service pack for SQL Server 2005

WORKAROUND

To work around this problem, follow these steps:

1. 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

2. 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

3. 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.

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.

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

Steps to reproduce the problem

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

5. 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:

<empty>
30

Note In this output, <empty> indicates that nothing appears.

However, the output is expected to be similar to the following:

30
30

REFERENCES

For more information about the data function for XQuery, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms187038.aspx

Keywords: kbtshoot kbqfe kbprb kbexpertiseadvanced kbsql2005tsql kbsql2005xml kbsql2005presp1fix KB914638

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.