WriteXml grouping data

[Stephen Witter]

I am using WriteXml to output data to an xml file, and and XML web
control to display it. The code runs fine, however my groups are
being ignored. For Instance, the current output is:

<NewDataSet>
<Table>
<PRODUCT_CODE>RM-Safety</PRODUCT_CODE>
<PART_ID>*61177-000</PART_ID>
<PART_DESCRIPTION>LABEL POUCH 1 PACK SOLO KR
AXIA</PART_DESCRIPTION>
<LABOR_COST>0</LABOR_COST>
<MATERIAL_COST>0.061000</MATERIAL_COST>
<BURDEN_COST>0</BURDEN_COST>
<SERVICE_COST>0</SERVICE_COST>
<TOTAL_VALUE>0</TOTAL_VALUE>
</Table>
<NewDataSet>

The "PRODUCT_CODE" is the group. I would like it to be:

<NewDataSet>
<Table>
<PRODUCT_GROUP>
<PRODUCT_CODE>RM-Safety</PRODUCT_CODE>
<DETAIL>
<PART_ID>*61177-000</PART_ID>
<PART_DESCRIPTION>LABEL POUCH 1 PACK SOLO KR
AXIA</PART_DESCRIPTION>
<LABOR_COST>0</LABOR_COST>
<MATERIAL_COST>0.061000</MATERIAL_COST>
<BURDEN_COST>0</BURDEN_COST>
<SERVICE_COST>0</SERVICE_COST>
<TOTAL_VALUE>0</TOTAL_VALUE>
</DETAIL>
</PRODUCT_GROUP>
</Table>
<NewDataSet>

My query is using a group by clause but the data always comes out the
same. Here is my code:

Dim SelectCommand As String = "SELECT top 100 PRODUCT_CODE,
PART_ID, PART_DESCRIPTION, LABOR_COST, MATERIAL_COST, " _
& "BURDEN_COST, SERVICE_COST,
TOTAL_VALUE FROM RV_INVENTORY_VALUATION_MONTHEND " _
& "GROUP BY PRODUCT_CODE, PART_ID,
PART_DESCRIPTION, LABOR_COST, MATERIAL_COST, " _
& "BURDEN_COST, SERVICE_COST,
TOTAL_VALUE"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand,
myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

ds.WriteXml(server.mappath("../Administration/Month End
Inventory Valuation.xml"), XmlWriteMode.IgnoreSchema)

I appreciate any help.

 

[Guest]

The name <Table> comes from the fact you did not use TableMappings on your
DataAdapter. It is a default name. If you use TableMappings, you can get
<PRODUCT_GROUP> as a table name tag.

To get child info under <DETAIL> you will have to create a DETAIL table
(TableMapping) and add a relationship.

If this is not possible, run the XML through XSLT to reorg as you wish.


---

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

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

 

[Stephen Witter]

Thanks for the quick response Cowboy, however I think I used a bad
example. What I am actually looking for is structuring my data much
like a report, within a table using grouping:

-------------------------------------
Group1 <--merged cells
-------------------------------------
| price1 | description1 |
-------------------------------------
| price2 | description2 |
-------------------------------------
| price3 | description3 |
-------------------------------------
Group2 <--merged cells
-------------------------------------
| price1 | description1 |
-------------------------------------
| price2 | description2 |
-------------------------------------
| price3 | description3 |
-------------------------------------

I am placing the xml result in a table using xslt and want to merge
all columns in a row for the groups, while having the details in
cells, etc... I have looked at SQL Servers FOR XML EXPLICIT and union
queries, but it seems very cumbersome.

Thanks again.