Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;