Microsoft KB Archive/914638

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



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:   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  represents the name of the table. represents the name of the XML table column. represents the name of the old problematic schema collection. represents the name of the new schema collection. 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 &quot;Applies to&quot; 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 &quot;3.4.3&quot; section and the &quot;3.4.4&quot; section in the following Web site:

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.</li>  Paste the following code example in the Query Editor window: create xml schema collection myschema as N' <xs:schema xmlns:xs=&quot;http://www.w3.org/2001/XMLSchema&quot;> <xs:element name=&quot;ElementName&quot; type=&quot;xs:integer&quot; /> <xs:complexType name=&quot;newct&quot; mixed=&quot;true&quot;> <xs:simpleContent> <xs:extension base=&quot;xs:integer&quot;> <xs:attribute name=&quot;bar&quot; type=&quot;xs:integer&quot; /> </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=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xsi:type=&quot;newct&quot; bar=&quot;10&quot;>30</ElementName>') go

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

select xmlcol.query('data(/ElementName)') from xml_test </li></ol>

The output is similar to the following: 30 Note In this output, indicates that nothing appears.

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

<div class="references_section">