Microsoft KB Archive/296393

= PRB: Attributes Are Not Generated for Fields That Contain a NULL Value When ADO Recordset Is Persisted in XML =

Article ID: 296393

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 2
 * Microsoft SQL Server 7.0 Service Pack 3

-



This article was previously published under Q296393



SYMPTOMS
When an ADO Recordset object is persisted in XML format,  element attributes are not generated for fields in its records that have a NULL value. This behavior may impact generic Extensible Stylesheet Language Transformation (XSLT) scripts that are written to transform ADO-persisted XML to an alternate format. Most likely, you will notice the impact when the @* XML Path Language (XPath) expression is used to access the attributes of  elements that represent the fields of a record.

This behavior does not have any negative impact if you only use ADO to re-open and manipulate the persisted recordset. It also does not have a negative impact on XSLT scripts that are written for a specific ADO-persisted XML that references the  element attributes using their hard-coded names. The only negative impact that it may have is on XSLT scripts that use the @* XPath expression to reference the  element attributes. Because the attributes to represent the fields with NULL values are absent, the XSL Transformation output may appear incorrectly aligned or formatted.



CAUSE
This behavior is by design. See the &quot;More Information&quot; section for a detailed explanation.



RESOLUTION
If the SQL implementation of the target data source supports an extension such as the T-SQL IsNull function of Microsoft SQL Server, you can use this function to construct a query that returns an alternate fixed value for a column when a NULL value is encountered. However, note that this renders a non-updateable recordset.



MORE INFORMATION
Each record in an ADO Recordset that is persisted in XML format is represented by a  element in the generated XML. The fields in a record are written out as attributes of the  element that represents the record. Every XML attribute must be assigned a value that is enclosed in a pair of single or double quotation marks. There is no equivalent in plain text to denote a NULL value. A pair of quotation marks with no value to represent an empty string is not the same as a NULL value.

Steps to Reproduce Behavior
  Run the following T-SQL script in one of your SQL Server databases to create a sample table that includes test records: create table Employee ( emp_id int primary key, emp_name varchar(20), hire_date datetime, emp_level int )

Insert into Employee values (1,'Jack','1/1/2001',70) Insert into Employee values (2,'John','2/1/2001',NULL) Insert into Employee values (3,'Peter',NULL,NULL) Insert into Employee values (4,'Pam',NULL,50)  Create a new Standard EXE project in Visual Basic. Form1 is create by default. From the Project menu, click Reference, and then select the Microsoft ActiveX Data Objects 2.x Library check box. Make sure that you use ADO version 2.1 or later.</li> Add a CommandButton control to Form1.</li>  Copy and paste the following code in the Click event procedure of the command button.

Note You must change User ID =<UID> and password = to the correct values before you run this code. Make sure that <User ID> has the appropriate permissions to perform this operation on the database. Dim cn As ADODB.Connection Dim rs As New ADODB.Recordset

Set cn = New ADODB.Connection Set rs = New ADODB.Recordset

cn.Open &quot;Provider=SQLOLEDB;Data Source=SQl Server;&quot; & _ &quot;Initial Catalog=Database;User Id=<UID>;Password= ;&quot; rs.CursorLocation = adUseClient rs.Open &quot;Select * from Employee&quot;, cn, adOpenStatic, adLockReadOnly

If Dir(&quot;c:\ADOXMLNULLTest.xml&quot;) <> &quot;&quot; Then Kill &quot;c:\ADOXMLNULLTest.xml&quot; End If

rs.Save &quot;c:\ADOXMLNULLTest.xml&quot;, adPersistXML

rs.Close Set rs = Nothing

cn.Close Set cn = Nothing

MsgBox &quot;Employee Recordset has been persisted successfully !&quot; </li> Modify the ADO connection string in the cn.Open statement to point to the SQL Server database in which you created the sample table in step 1.</li> Save and run the project. Click Command1 when the form is displayed to run the Visual Basic ADO code that opens an ADO Recordset and persists it in XML format to disk. The SELECT statement that is specified as the Source parameter in the rs.Open statement is written to retrieve the data that you inserted into the sample table in step 1.</li> Stop running the project.</li>  In Microsoft Internet Explorer, open the persisted XML file. Examine the <z:row> elements that represent the individual records. Notice that attributes are not generated to represent the columns into which you inserted NULL values when you run the T-SQL script in step 1. The XML file that the rs.Save statement persists appears as follows: <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly'> <s:AttributeType name='emp_id' rs:number='1' rs:writeunknown='true'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/> </s:AttributeType> <s:AttributeType name='emp_name' rs:number='2' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='hire_date' rs:number='3' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='3' rs:precision='23' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='emp_level' rs:number='4' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/> </s:AttributeType> <s:extends type='rs:rowbase'/> </s:ElementType> </s:Schema> <rs:data> <z:row emp_id='1' emp_name='Jack' hire_date='2001-01-01T00:00:00' emp_level='70'/> <z:row emp_id='2' emp_name='John' hire_date='2001-02-01T00:00:00'/> <z:row emp_id='3' emp_name='Peter'/> <z:row emp_id='4' emp_name='Pam' emp_level='50'/> </rs:data>

Workaround
</li>  Replace the query that is specified as the Source parameter of the rs.Open statement in the Click event procedure of the command button with the following SQL SELECT statement: Select emp_id, emp_name,isnull(hire_date,) as hire_date,isnull(emp_level,) as emp_level from Employee </li> Save and run the project. Click Command1 when the form is displayed to run the Visual Basic ADO code that opens the ADO Recordset and persists it in XML format to disk.</li>  In Internet Explorer, open the persisted XML file. Examine the <z:row> elements. Notice that attributes have been generated for the columns with NULL values. This is achieved by using the T-SQL IsNull function to return an alternate default value when a NULL is encountered in the hire_date or emp_name columns. The persisted XML file appears as follows: <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly'> <s:AttributeType name='emp_id' rs:number='1' rs:writeunknown='true'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/> </s:AttributeType> <s:AttributeType name='emp_name' rs:number='2' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='hire_date' rs:number='3'> <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='3' rs:precision='23' rs:fixedlength='true' rs:maybenull='false'/> </s:AttributeType> <s:AttributeType name='emp_level' rs:number='4'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/> </s:AttributeType> <s:extends type='rs:rowbase'/> </s:ElementType> </s:Schema>

<rs:data> <z:row emp_id='1' emp_name='Jack' hire_date='2001-01-01T00:00:00' emp_level='70'/> <z:row emp_id='2' emp_name='John' hire_date='2001-02-01T00:00:00' emp_level='0'/> <z:row emp_id='3' emp_name='Peter' hire_date='1900-01-01T00:00:00' emp_level='0'/> <z:row emp_id='4' emp_name='Pam' hire_date='1900-01-01T00:00:00' emp_level='50'/> </rs:data> </li></ol>

Keywords: kbprb kbmsxmlnosweep KB296393

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.