Solved

Web Service Not Returning XML Tag When Column Is Null

Posted on 2011-09-28
15
507 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Beircheart
  • 8
  • 7
15 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36770270
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
 

Author Comment

by:Beircheart
ID: 36813471
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
 

Author Comment

by:Beircheart
ID: 36813506
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36815488
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
 

Author Comment

by:Beircheart
ID: 36815533
So where does that leave us?
0
 

Author Comment

by:Beircheart
ID: 36815556
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36815563
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 36815696
I don't have any problem with my IE8, so it appears to be local to your browser.
0
 

Assisted Solution

by:Beircheart
Beircheart earned 0 total points
ID: 36815703
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
 

Author Comment

by:Beircheart
ID: 36815712
Re: IE8

Must be some local security setting. Thanks for letting me know! :)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36816471
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
 

Author Comment

by:Beircheart
ID: 36890410
Hi,

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

Regards,
Ben
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 36891148
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 36891159
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
 

Author Closing Comment

by:Beircheart
ID: 37068276
Hi,
I would not consider the solution complete. Hence, I'm offering a B
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Xsl Nested Loop 4 22
C# Delete from XML 10 39
RSS Feed Enclosure URL 1 67
Specific format 21 144
The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now