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

MORE INFORMATION

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>
   					

   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>