ReadXMLSchema Doesn't Work

[Guest]

I am attempting to apply a schema to a data set, and it doesn’t seem to work
at all. It’s like my data set completely ignores ReadXMLSchema. To test this,
I ran the code below:

ds.ReadXmlSchema("E:\\ foo.xsd");
Response.Write("<pre>");
Response.Write(System.Web.HttpUtility.HtmlEncode(ds.GetXmlSchema()));
Response.Write("</pre>");

What is written out does not match what is in my foo.xsd file.

foo.xsd looks like this:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="SampleQuarter" type="xs:string"
minOccurs="1" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

This is what is written out. Notice the change in type and minOccurs.

<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="SampleQuarter" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

Any help would be appreciated.

 

[Kevin Yu [MSFT]]

Hi Dale,

I tried with exactly the same code you have provided on my machine,
however, I get the correct response from the output. Could you also include
the code that you create the ds(DataSet)? Have you changed other properties
on the DataSet or have you applied other schema before?

Also, you can run my test code with a clean DataSet object to see if the
output is correct.

DataSet ds = new DataSet();
ds.ReadXmlSchema("E:\\foo.xsd");
Response.Write("<pre>");

Response.Write(System.Web.HttpUtility.HtmlEncode(ds.GetXmlSchema()));
Response.Write("</pre>");

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Hi Kevin,

Thank you for your reply.

The code that you sent works fine with the exception that it drops the
minOccurs attribute. Unfortunately, I need this attribute to stay intact.

I am using the SqlHelper Enterprise Application Block to create a dataset
from a stored procedure. The dataset is totally ignoring ReadXMLSchema when I
create the dataset this way. A sample of the code is below.

System.Data.DataSet ds;

ds = SqlHelper.ExecuteDataset(Globals.AppSettings.ConnectionString,
"storedProcedureName");

ds.ReadXmlSchema("E:\\webdocs\\sap\\sample\\foo.xml");
Response.Write("<pre>");
Response.Write(System.Web.HttpUtility.HtmlEncode(ds.GetXmlSchema()));
Response.Write("</pre>");

Thanks for your help.

Dale

 

[Kevin Yu [MSFT]]

Hi Dale,

As far as I know, the default value for minOccurs is 1, so the attribute is
omitted.

It is the SqlHelper that make the schema not upated. Because when
ExecuteDataSet, the schema for ds is already created, which is determined
by the database types. So you have to put the ReadXmlSchema before
executing DataSet.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

How can I apply ReadXmlSchema before ExecuteDataSet? ExecuteDataSet creates
an instance of a DataSet object. Is it possible to create a DataSet object,
apply a schema, and then populate the DataSet from a database query? Can you
provide some sample code?

 

[Kevin Yu [MSFT]]

Hi Dale,

Yes, as you know, we can create a DataSet using

DataSet ds = new DataSet();

Then load the schema with

ds.ReadXmlSchema(@"c:\foo.xsd");

Then you can fill the DataSet with a DataAdapter. Or, if you wan't to use
SqlHelper class, you can use SqlHelper.FillDataset method to put data into
ds.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Thank you very much, Kevin. That works as expected. However, I still have an
issue. I am getting a “Failed to enable constraints†error. This will make
more sense if I start from the beginning.

I am trying to output the results of a stored procedure to an Excel file.
Below is the code:

System.Data.DataSet ds;
ds = SqlHelper.ExecuteDataset(AppSettings.ConnectionString,
"spSample_GetSampleFile");

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;
filename=SAPSample.xls");
Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
Response.Write(ds.GetXml());
Response.End();

The underlying problem is that if an entire column has all null values, the
column is excluded from the XML entirely. As an example, the following query
will result in an excel file with only one column:

SELECT 1 AS Column1, NULL AS Column2

Is there anyway to tell the dataset to output all columns, even if they
contain null values? I thought minOccurs would take care of this, but it
places a non-null constraint on the column.

Thanks,

Dale

 

[Kevin Yu [MSFT]]

Hi Dale,

As far as know, from dataset's point of view, we cannot do that. Because
the output Xml is for dataset use only, not for generating the Excel file.
You can try to post in the Excel group to see if they have any suggestions
on this issue.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

The excel file isn’t a problem at all. That portion of the code works
flawlessly. The problem is the columns that are excluded from the xml due to
null values. Is there any way to output the xml with all columns regardless
of if there are null values or not?

 

[Kevin Yu [MSFT]]

Hi Dale,

If you want to use DataSet.GetXml to generate the Excel file. The only way
is to add the element to the xml for this column. You can either achieve
this by

1. If the column is a string, replace it with string.Empty.
2. Use XSLT to add the element.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)