Solved

Web Service Not Returning XML Tag When Column Is Null

Posted on 2011-09-28
15
511 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Nessus Scan 1 71
Infopath people picker - shading 3 122
Use info Path to upload a new document into a SharePoint document library 7 49
.net and XML report from SCCM 5 24
Here I am going to explain creating proxies at runtime for WCF Service. So basically we use to generate proxies using Add Service Reference and then giving the Url of the WCF service then generate proxy files at client side. Ok, what if something ge…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 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