Solved

Web Service Not Returning XML Tag When Column Is Null

Posted on 2011-09-28
15
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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