Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Web Service Not Returning XML Tag When Column Is Null

Posted on 2011-09-28
15
Medium Priority
?
526 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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

660 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