You receive a NULL value when you use XQuery to retrieve the value of an XML element in SQL Server 2005 (914638)
The information in this article applies to:
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
Bug #: 20011171 (SQLBUDT)
SYMPTOMSYou 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.
CAUSEThis 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.WORKAROUNDTo 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. STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.REFERENCESFor more information about the data function for XQuery, visit the following Microsoft Developer Network (MSDN) Web site:
Modification Type: | Major | Last Reviewed: | 4/6/2006 |
---|
Keywords: | kbsql2005presp1fix kbsql2005xml kbsql2005tsql kbExpertiseAdvanced kbtshoot kbprb KB914638 kbAudDeveloper kbAudITPRO |
---|
|