Web Service Not Returning XML Tag When Column Is Null

Hi,

I have a web service that is being consumed by an Infopath 2010 form. Here's a brief outline of how the web service works:

The web service queries an Oracle database. It stores the result in a dataset. It then loads the dataset in to an XMLDocument type. Returns XMLDocument

When I query the web service, it returns most of the data as expected. However, if certain columns returned by the query are null, then there is no trace of that column in the returned XMLDocument. For example:

If <field2/> was null, I'd like to see:

<root>
<field1>First Value</field1>
<field2></field2>
<field3>Third Value</field3>
</root>

But I'm getting:
<root>
<field1>First Value</field1>
<field3>Third Value</field3>
</root>

I've searched for several hours trying to find a solution and have repeatedly come close but still havent found a solution.

My question is, how can I force the web service to include the tags of the null columns? I presume I need to incorporate the schema but how?

I repeat, I've searched for several hours so I've probably followed most of the relevant links so I'm asking you in the nicest possible way, please don't point me towards a link unless you have actually witnessed it solving the problem I've described

Thanks in advance,
Ben
BeircheartAsked:
Who is Participating?
 
Bob LearnedCommented:
I would also look into JSON serialization, if you have 3.5 or higher framework version.

How to: Serialize and Deserialize JSON Data
http://msdn.microsoft.com/en-us/library/bb412179.aspx
Dim person As New Person()
p.name = "John"
p.age = 42

Using stream1 As New MemoryStream()
    Dim serializer As New DataContractJsonSerializer(GetType(Person))
    serializer.WriteObject(stream1, p)
End Using

Open in new window

0
 
Bob LearnedCommented:
What kind of web service are you working with (ASP.NET, WCF, ...)?  If you have a WCF service, what does the data contract look like?  Do you have "xs:nillable=true" in the WSDL?
0
 
BeircheartAuthor Commented:
Hi LearnedOne,

It's an ASP.Net Wed Service Application (Using VB.Net). I haven't done anything with the xs:nillable attribute. Do I need to? If so, could you detail what I need to do please?

Thank you for your time and effort,
Ben
<xs:element name="DesignTeam" msprop:BaseTable.0="TMP_APRC_PRG_DESIGNTEAM">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="PRG_CODE" msprop:OraDbType="126" msprop:BaseColumn="PRG_CODE" type="xs:string" minOccurs="0" />
                <xs:element name="NAME" msprop:OraDbType="126" msprop:BaseColumn="NAME" type="xs:string" minOccurs="0" />
                <xs:element name="DEPT" msprop:OraDbType="126" msprop:BaseColumn="DEPT" type="xs:string" minOccurs="0" />
                <xs:element name="AFFILIATION" msprop:OraDbType="126" msprop:BaseColumn="AFFILIATION" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
BeircheartAuthor Commented:
I should have mentioned in my previous post:

The attached code above is a section of the schema generated from the dataset. i.e. MyDataset.WriteXmlSchema(Server.MapPath("TestSchema.xsd"))

This section is only returning:

<DesignTeam>
  <PRG_CODE>My Programme Code</PRG_CODE>
</DesignTeam>
0
 
Bob LearnedCommented:
Let me get in my way back machine, and remember DataSet schemas...oh, now I remember the reason why I quickly went away from that construct....it doesn't support nullable types (d'oh).

0
 
BeircheartAuthor Commented:
So where does that leave us?
0
 
BeircheartAuthor Commented:
As an aside, ever since I added the comment that has embedded code, I can't view this particular page using Internet Explorer 8... so I'm using Firefox for the moment. Every other Experts Exchange page is fine.

Have you the same problem or is it local to my browser?
0
 
Bob LearnedCommented:
One experiment:  open the typed DataSet designer, and find the field you want to modify, and set AllowDBNull = true (I have no idea what effect that will have).  If you have the luxury of finding a new way to achieve the same effect, I would look into WCF and a data contract that allows null members.
0
 
Bob LearnedCommented:
I don't have any problem with my IE8, so it appears to be local to your browser.
0
 
BeircheartAuthor Commented:
Hi, it didn't seem to have any effect I'm afraid.

I've created a function that converts any DBNull values in a dataset to "" or 0 or a valid date depending on their datatype. This is not an ideal workaround as there is at least 1 drawback that I've noticed so far. If there is no row in a table, no values are created. To fix this, I tried manually adding a row but this is providing false data so I removed the AddRow. See attached code:

I'm going to hold off on closing this question in the hope of finding a more ideal solution...
Function ReplaceDataSetNulls(ByVal MyDataSet)

        For Each MyDataSetTable As DataTable In MyDataSet.Tables
            For Each MyDataSetRow As DataRow In MyDataSetTable.Rows
                For Each MyDataSetColumn As DataColumn In MyDataSetTable.Columns

                    If MyDataSetRow(MyDataSetColumn.ColumnName) Is DBNull.Value Then

                        If MyDataSetColumn.DataType.Name = "String" Then
                            MyDataSetRow(MyDataSetColumn.ColumnName) = ""
                        ElseIf MyDataSetColumn.DataType.Name = "Int32" Then
                            MyDataSetRow(MyDataSetColumn.ColumnName) = 0
                        ElseIf MyDataSetColumn.DataType.Name = "DateTime" Then
                            MyDataSetRow(MyDataSetColumn.ColumnName) = DateTime.Parse("01/01/1000")
                        End If

                    End If

                Next
            Next
        Next

        Return True

    End Function

Open in new window

0
 
BeircheartAuthor Commented:
Re: IE8

Must be some local security setting. Thanks for letting me know! :)
0
 
Bob LearnedCommented:
You might also look at a custom XML serialization scheme, that you can control, since you have no control over how the DataSet gets serialized.
0
 
BeircheartAuthor Commented:
Hi,

I am not familiar with XML Serialization and how it is implemented. Could you provide a basic example please?

Regards,
Ben
0
 
Bob LearnedCommented:
HOW TO: Serialize and Deserialize XML in Visual Basic .NET
http://support.microsoft.com/kb/316730
'Set up product object.
    Dim p As New clsProduct()
    p.Name = "Widget"
    p.Description = "Faster, better, cheaper"
    p.Qty = 5

    'Serialize object to a text file.
    Dim objStreamWriter As New StreamWriter("C:\Product.xml")
    Dim x As New XmlSerializer(p.GetType)
    x.Serialize(objStreamWriter, p)
    objStreamWriter.Close()

    'Deserialize text file to a new object.
    Dim objStreamReader As New StreamReader("C:\Product.xml")
    Dim p2 As New clsProduct()
    p2 = x.Deserialize(objStreamReader)
    objStreamReader.Close()

    'Display property values of the new product object.
    Console.WriteLine(p2.Name)
    Console.WriteLine(p2.Description)
    Console.WriteLine(CStr(p2.Qty))
    Console.ReadLine()

Open in new window

0
 
BeircheartAuthor Commented:
Hi,
I would not consider the solution complete. Hence, I'm offering a B
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.