Dataset.GetXML() with the power of FOR XML EXPLCIT?

[Mike]

Can anyone tell me if this is possible?

We have several ASP/SQL Server 2000/XSLT applications with hundreds of
stored procedures returning data using FOR XML EXPLICIT. What we
would like to do is port to .NET and support numerous different
databases by changing the stored procedures to return "normal"
resultsets (i.e. a recordset), then convert the recordset to XML and
pass to the XSLT to generate the HTML output.

I have seen the GetXML() method of the Dataset object but this seems
to only be able to generate simple XML output. By that I mean a
simple table with rows and columns would produce:

<table>
<row>
<column1>data</column1>
<column2>data</column2>
<column3>data</column3>
....
</row>
<row>...</row>
</table>

Is there a way to produce more heirarchical XML as I can generate
using the FOR XML EXPLICIT syntax and multiple table joins? e.g.

<order ordernumber="5" vat="4.17" deliverycharge="3.95"
totalcost="36.02" orderdate="2004-01-22 11:44:58">
<emailaddress><![CDATA[[email protected]]]></emailaddress>
<itemtype itemtypeid="171" cost="27.90" costhasnulls="0">
<title><![CDATA[Containers]]></title>
<msgs><![CDATA[]]></msgs>
<orderorderitem id="6" productcode="SH127" quantity="1">
<title><![CDATA[Cylinder]]></title>
<deliverydetails><![CDATA[e<br/>f<br/>g<br/>h<br/>United
Kingdom]]></deliverydetails>
<invoicedetails><![CDATA[a<br/>b<br/>c<br/>d<br/>Afghanistan]]></invoicedetails>
<notes><![CDATA[]]></notes>
<dispatch/>
</orderorderitem>
<orderorderitem>
...
</orderorderitem>
</itemtype>
<itemtype>
...
</itemtype>
</order>

Thanks in advance,

Mike.

 

[Cowboy \(Gregory A. Beamer\)]

The XML has to be in DataSet format to work. As such, I would not try to mix
ASP methodology with ASP.NET as the XML is likely to end up incompatible.

The best method to learn the proper syntax is created a DataSet and output
to XML. Once you have this map, you will be able to create your matrix for
your XML EXPLICIT. It is rather easy once you see the rules.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

Can anyone tell me if this is possible?

We have several ASP/SQL Server 2000/XSLT applications with hundreds of
stored procedures returning data using FOR XML EXPLICIT. What we
would like to do is port to .NET and support numerous different
databases by changing the stored procedures to return "normal"
resultsets (i.e. a recordset), then convert the recordset to XML and
pass to the XSLT to generate the HTML output.

I have seen the GetXML() method of the Dataset object but this seems
to only be able to generate simple XML output. By that I mean a
simple table with rows and columns would produce:

<table>
<row>
<column1>data</column1>
<column2>data</column2>
<column3>data</column3>
....
</row>
<row>...</row>
</table>

Is there a way to produce more heirarchical XML as I can generate
using the FOR XML EXPLICIT syntax and multiple table joins? e.g.

<order ordernumber="5" vat="4.17" deliverycharge="3.95"
totalcost="36.02" orderdate="2004-01-22 11:44:58">
<emailaddress><![CDATA[[email protected]]]></emailaddress>
<itemtype itemtypeid="171" cost="27.90" costhasnulls="0">
<title><![CDATA[Containers]]></title>
<msgs><![CDATA[]]></msgs>
<orderorderitem id="6" productcode="SH127" quantity="1">
<title><![CDATA[Cylinder]]></title>
<deliverydetails><![CDATA[e<br/>f<br/>g<br/>h<br/>United
Kingdom]]></deliverydetails>

<notes><![CDATA[]]></notes>
<dispatch/>
</orderorderitem>
<orderorderitem>
...
</orderorderitem>
</itemtype>
<itemtype>
...
</itemtype>
</order>

Thanks in advance,

Mike.

 

[Mike]

Thanks for your reply but I think I didn't explain the problem well
enough.

At the minute we have (from the database layer forward):

- complex stored procedures using FOR XML EXPLICIT and multiple JOINs
to create a heirarchical XML output
- that is called via ADO and the XML passed back through an ADO Stream
- that is then transformed using XSLT to produce HTML format

When moving to ASP.NET, we would like to keep the XSLT layer as we
have heavy investment in it already so we still need data in XML
format. The way I see things changing in the .NET world goes like
this:

- the single complex stored procedure is broken out into several
simple SPs that return a normal resultset (basically the queries that
are the different parts of the JOINs)
- each of these simple SPs is called and added into a DataSet
- these resultsets are then joined together using DataRelations
- the Nested property(ies) is/are set to TRUE
- then call GetXML() and this should result (if the DataRelations are
setup correctly) in the same XML output as the complex SP using JOINs
and FOR XML EXPLICIT

So I have two questions:
1. Are my assumptions above correct?
2. Surely, from a performance point of view, this is terrible?

From performing one, albeit complex, operation all on the database we
are moving to calling multiple SPs, then (on the web server) doing the
joins and the conversion to XML. I can't honestly believe that this
method doesn't suffer some poor performance problems (specifically the
multiple SP calling compared to a single call and then doing database
JOINs outside of the database server!!!), but please correct me if I
am wrong.

Thanks in advance,

Mike.

The XML has to be in DataSet format to work. As such, I would not try to mix
ASP methodology with ASP.NET as the XML is likely to end up incompatible.

The best method to learn the proper syntax is created a DataSet and output
to XML. Once you have this map, you will be able to create your matrix for
your XML EXPLICIT. It is rather easy once you see the rules.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

Can anyone tell me if this is possible?

We have several ASP/SQL Server 2000/XSLT applications with hundreds of
stored procedures returning data using FOR XML EXPLICIT. What we
would like to do is port to .NET and support numerous different
databases by changing the stored procedures to return "normal"
resultsets (i.e. a recordset), then convert the recordset to XML and
pass to the XSLT to generate the HTML output.

I have seen the GetXML() method of the Dataset object but this seems
to only be able to generate simple XML output. By that I mean a
simple table with rows and columns would produce:

<table>
<row>
<column1>data</column1>
<column2>data</column2>
<column3>data</column3>
....
</row>
<row>...</row>
</table>

Is there a way to produce more heirarchical XML as I can generate
using the FOR XML EXPLICIT syntax and multiple table joins? e.g.

<order ordernumber="5" vat="4.17" deliverycharge="3.95"
totalcost="36.02" orderdate="2004-01-22 11:44:58">
<emailaddress><![CDATA[[email protected]]]></emailaddress>
<itemtype itemtypeid="171" cost="27.90" costhasnulls="0">
<title><![CDATA[Containers]]></title>
<msgs><![CDATA[]]></msgs>
<orderorderitem id="6" productcode="SH127" quantity="1">
<title><![CDATA[Cylinder]]></title>
<deliverydetails><![CDATA[e<br/>f<br/>g<br/>h<br/>United
Kingdom]]></deliverydetails>

<notes><![CDATA[]]></notes>
<dispatch/>
</orderorderitem>
<orderorderitem>
...
</orderorderitem>
</itemtype>
<itemtype>
...
</itemtype>
</order>

Thanks in advance,

Mike.

 

[Cowboy \(Gregory A. Beamer\)]

In .NET there are a couple ways you can go, depending on the nature of how
you do business. For example, here are three.

1. Continue with the current XML/XSLT method, but update the engine a bit.
When I wrote the book ADO.NET and XML: ASP.NET on the Edge, I put together
an XML/XSLT model (admitedly not as complex as yours) that renders the site
from XSLT. The download code for these chapters is on the Wiley extras site
(http://www.wiley.com/extras) - look for the book title. To continue this
methodology, the XML becomes a pass through, so it does not fit scenarios
where you consume and bind data.

2. Reconfigure your XML EXPLICIT statements to fit the XML format of the
..NET DataSet. The Con here is you end up reconfiging your legacy apps or
writing new sprocs.

3. Take the current XML output from your sproc and run an XSLT transform to
create DataSet XML. This allows you to leverage your current legacy apps by
using identical stored procedures. This method is similar to method 1, but
you are running the transform on the data, rather than outputting to HTML.
You can then consume the data at will.

REST INLINE

Thanks for your reply but I think I didn't explain the problem well
enough.

At the minute we have (from the database layer forward):

- complex stored procedures using FOR XML EXPLICIT and multiple JOINs
to create a heirarchical XML output
- that is called via ADO and the XML passed back through an ADO Stream
- that is then transformed using XSLT to produce HTML format

When moving to ASP.NET, we would like to keep the XSLT layer as we
have heavy investment in it already so we still need data in XML
format. The way I see things changing in the .NET world goes like
this:

- the single complex stored procedure is broken out into several
simple SPs that return a normal resultset (basically the queries that
are the different parts of the JOINs)

Or, you can place multiple SELECT queries into a single sproc and return
multiple tables from one point. The choice is yours. I tend to try to make
one trip to the database for all of my data for one page, so multiple
SELECTS in a single sproc is a better choice for me.

- each of these simple SPs is called and added into a DataSet

If you run this in one sproc, you do not have to combine on the middle tier.

- these resultsets are then joined together using DataRelations

This is a good idea.

- the Nested property(ies) is/are set to TRUE
- then call GetXML() and this should result (if the DataRelations are
setup correctly) in the same XML output as the complex SP using JOINs
and FOR XML EXPLICIT

So I have two questions:
1. Are my assumptions above correct?

This is certainly an option.

2. Surely, from a performance point of view, this is terrible?

As described, perhaps. But, using a single sproc with a SELECT for each
dataTable in your DataSet can eliminate most, if not all, of the perf hit.

In your scenario:

conn.Open();
da1.Fill(ds1);
....
daN.Fill(dsN);
conn.Close();

//Pull Data Tables from each into a single stored procedure
ds.Tables.Add(ds1.Tables[0]);
....
ds.Tables.Add(dsN.Tables[0]);

But, with multiple selects in a single DB, you end up with:

conn.Open();
da.Fill(ds);
conn.Close();

Console.WriteLine(ds.Tables.Count); // = N - 1

You have eliminated the looping here, which reduces the load tremendously.
Now, you only have the "overhead" of adding relationships and outputting
XML.

From performing one, albeit complex, operation all on the database we
are moving to calling multiple SPs, then (on the web server) doing the
joins and the conversion to XML. I can't honestly believe that this
method doesn't suffer some poor performance problems (specifically the
multiple SP calling compared to a single call and then doing database
JOINs outside of the database server!!!), but please correct me if I
am wrong.

You are right, but in the way you are thinking. It is quite easy to get
boxed in to one way when you start thinking about a problem. By stacking in
your sproc you eliminate building the DataSet manually. This is one
potential improvement.

As stated before, you can also use your old XSLT/XML system, with the new
..NET objects. If you are not consuming the data in binding, like binding a
DataView to a DataGrid, there is no need to do anything but retrieve the
XML. (NOTE: Yukon adds additional capabilities in the XML world). You can
then transform (with a bit harder syntax) in the same manner you are used
to.

If you are not consuming the DataSet via binding, there is little reason to
do anything other than retrieve the XML. Now, the question comes down to
whether a DataSet is necessary at all. It might be cheaper, perf wise, to
get your XML in a DataReader and consume before you close the connection.

Ask the questions:
1. Am I using the DataSet for anything other than creating my XML?

If no, then simply pull the XML you are now creating and be done with it. As
stated, you can pull and consume from a DataReader much more efficiently
than a DataSet.

If yes, then pull all of the tables at once rather than a bite at a time.
Otherwise, you can kill perf. If the single table sprocs make sense in other
apps, create a master sproc that execs the other sprocs for output of all
tables in one shot. Either way, you should be able to create the entire
DataSet without multiple hits.

2. Is there any future benefit to making the XML compatible with the MS
DataSet XML?

This is a harder question. If yes, you can either use the multiple pull from
a single sproc, or alter the FOR XML EXPLICIT to match the MS format. If you
know you will never use DataSet XML without retooling your organization, or
at least the apps in question, then DataSet compatibility of the XML is a
feature that is not needed.

Hope this helps.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Mike]

Thanks again for your reply. We've got a lot to think about and a bit
of playing around to do as well.

In .NET there are a couple ways you can go, depending on the nature of how
you do business. For example, here are three.

1. Continue with the current XML/XSLT method, but update the engine a bit.
When I wrote the book ADO.NET and XML: ASP.NET on the Edge, I put together
an XML/XSLT model (admitedly not as complex as yours) that renders the site
from XSLT. The download code for these chapters is on the Wiley extras site
(http://www.wiley.com/extras) - look for the book title. To continue this
methodology, the XML becomes a pass through, so it does not fit scenarios
where you consume and bind data.

2. Reconfigure your XML EXPLICIT statements to fit the XML format of the
.NET DataSet. The Con here is you end up reconfiging your legacy apps or
writing new sprocs.

3. Take the current XML output from your sproc and run an XSLT transform to
create DataSet XML. This allows you to leverage your current legacy apps by
using identical stored procedures. This method is similar to method 1, but
you are running the transform on the data, rather than outputting to HTML.
You can then consume the data at will.

REST INLINE

Thanks for your reply but I think I didn't explain the problem well
enough.

At the minute we have (from the database layer forward):

- complex stored procedures using FOR XML EXPLICIT and multiple JOINs
to create a heirarchical XML output
- that is called via ADO and the XML passed back through an ADO Stream
- that is then transformed using XSLT to produce HTML format

When moving to ASP.NET, we would like to keep the XSLT layer as we
have heavy investment in it already so we still need data in XML
format. The way I see things changing in the .NET world goes like
this:

- the single complex stored procedure is broken out into several
simple SPs that return a normal resultset (basically the queries that
are the different parts of the JOINs)

Or, you can place multiple SELECT queries into a single sproc and return
multiple tables from one point. The choice is yours. I tend to try to make
one trip to the database for all of my data for one page, so multiple
SELECTS in a single sproc is a better choice for me.

- each of these simple SPs is called and added into a DataSet

If you run this in one sproc, you do not have to combine on the middle tier.

- these resultsets are then joined together using DataRelations

This is a good idea.

- the Nested property(ies) is/are set to TRUE
- then call GetXML() and this should result (if the DataRelations are
setup correctly) in the same XML output as the complex SP using JOINs
and FOR XML EXPLICIT

So I have two questions:
1. Are my assumptions above correct?

This is certainly an option.

2. Surely, from a performance point of view, this is terrible?

As described, perhaps. But, using a single sproc with a SELECT for each
dataTable in your DataSet can eliminate most, if not all, of the perf hit.

In your scenario:

conn.Open();
da1.Fill(ds1);
...
daN.Fill(dsN);
conn.Close();

//Pull Data Tables from each into a single stored procedure
ds.Tables.Add(ds1.Tables[0]);
...
ds.Tables.Add(dsN.Tables[0]);

But, with multiple selects in a single DB, you end up with:

conn.Open();
da.Fill(ds);
conn.Close();

Console.WriteLine(ds.Tables.Count); // = N - 1

You have eliminated the looping here, which reduces the load tremendously.
Now, you only have the "overhead" of adding relationships and outputting
XML.

From performing one, albeit complex, operation all on the database we
are moving to calling multiple SPs, then (on the web server) doing the
joins and the conversion to XML. I can't honestly believe that this
method doesn't suffer some poor performance problems (specifically the
multiple SP calling compared to a single call and then doing database
JOINs outside of the database server!!!), but please correct me if I
am wrong.

You are right, but in the way you are thinking. It is quite easy to get
boxed in to one way when you start thinking about a problem. By stacking in
your sproc you eliminate building the DataSet manually. This is one
potential improvement.

As stated before, you can also use your old XSLT/XML system, with the new
.NET objects. If you are not consuming the data in binding, like binding a
DataView to a DataGrid, there is no need to do anything but retrieve the
XML. (NOTE: Yukon adds additional capabilities in the XML world). You can
then transform (with a bit harder syntax) in the same manner you are used
to.

If you are not consuming the DataSet via binding, there is little reason to
do anything other than retrieve the XML. Now, the question comes down to
whether a DataSet is necessary at all. It might be cheaper, perf wise, to
get your XML in a DataReader and consume before you close the connection.

Ask the questions:
1. Am I using the DataSet for anything other than creating my XML?

If no, then simply pull the XML you are now creating and be done with it. As
stated, you can pull and consume from a DataReader much more efficiently
than a DataSet.

If yes, then pull all of the tables at once rather than a bite at a time.
Otherwise, you can kill perf. If the single table sprocs make sense in other
apps, create a master sproc that execs the other sprocs for output of all
tables in one shot. Either way, you should be able to create the entire
DataSet without multiple hits.

2. Is there any future benefit to making the XML compatible with the MS
DataSet XML?

This is a harder question. If yes, you can either use the multiple pull from
a single sproc, or alter the FOR XML EXPLICIT to match the MS format. If you
know you will never use DataSet XML without retooling your organization, or
at least the apps in question, then DataSet compatibility of the XML is a
feature that is not needed.

Hope this helps.