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

When an ADO Recordset object is persisted in XML format, <z:row/> 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 <z:row/> 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 <z:row/> 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 <z:row/> element attributes. Because the attributes to represent the fields with NULL values are absent, the XSL Transformation output may appear incorrectly aligned or formatted.

This behavior is by design. See the "More Information" section for a detailed explanation.

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.

Each record in an ADO Recordset that is persisted in XML format is represented by a <z:row/> element in the generated XML. The fields in a record are written out as attributes of the <z:row/> 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

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

2. Create a new Standard EXE project in Visual Basic. Form1 is create by default.
3. 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.
4. Add a CommandButton control to Form1.
5. Copy and paste the following code in the Click event procedure of the command button.
   
   Note You must change User ID =<UID> and password =<strong 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 "Provider=SQLOLEDB;Data Source=SQl Server;" & _
           "Initial Catalog=Database;User Id=<UID>;Password=<strong password>;"
   rs.CursorLocation = adUseClient
   rs.Open "Select * from Employee", cn, adOpenStatic, adLockReadOnly
   
   If Dir("c:\ADOXMLNULLTest.xml") <> "" Then
     Kill "c:\ADOXMLNULLTest.xml"
   End If
   
   rs.Save "c:\ADOXMLNULLTest.xml", adPersistXML
   
   rs.Close
   Set rs = Nothing
   
   cn.Close
   Set cn = Nothing
   
   MsgBox "Employee Recordset has been persisted successfully !"
   					

6. 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.
7. 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.
8. Stop running the project.
9. 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>
   </xml>
   					

   Back to the top

   Workaround

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

11. 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.
12. 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>
    </xml>