nesting XML nodes

7 Nov 2005 7:55 PM

bill

I am using ADO.net in VB.NET. I'm trying to export relational data from SQL
Server 2000 to XML with appropriate nesting.

I have included sample code below. Using a Northwind example for clarity,
the XML output is not nested - Customers are presented first, and then
Orders are presented at the same level as Customers. I want the Orders
nested in the correct Customers.

Is there a way to do this using .NET?

Thanks
Bill

[CODE]

Sub ExportToXML()
        Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
Customers", SqlConnection1)
        Dim orderDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
Orders", SqlConnection1)
        SqlConnection1.Open()
        Dim custDS As DataSet = New DataSet
        custDA.Fill(custDS, "Customers")
        orderDA.Fill(custDS, "Orders")
        Dim custOrderRel As DataRelation =
custDS.Relations.Add("CustOrders", _

custDS.Tables("Customers").Columns("CustomerID"), _

custDS.Tables("Orders").Columns("CustomerID"))
        custDS.WriteXml("c:\data\Output.xml")
    End Sub

[OUTPUT]
    <?xml version="1.0" standalone="yes" ?>
    <NewDataSet>
         <Customers>
              <CustomerID>ALFKI</CustomerID>
              <CompanyName>Alfreds Futterkiste</CompanyName>
                ...
         </Customers>
         <Orders>
              <OrderID>10643</OrderID>
              <CustomerID>ALFKI</CustomerID>
                ...
         </Orders>
    </NewDataSet>

[DESIRED OUTPUT]
    <?xml version="1.0" standalone="yes" ?>
    <NewDataSet>
         <Customers>
              <CustomerID>ALFKI</CustomerID>
              <CompanyName>Alfreds Futterkiste</CompanyName>
                ...
              <Orders>
                  <OrderID>10643</OrderID>
                  <CustomerID>ALFKI</CustomerID>
                   ...
             </Orders>
        </Customers>
    </NewDataSet>

7 Nov 2005 7:59 PM

bill

I found the answer - custOrderRel.nested = true. I should've looked harder
before posting.

Show quote

"bill" <bel***@datamti.com> wrote in message
news:%23uPrmU94FHA.1148@tk2msftngp13.phx.gbl...
> I am using ADO.net in VB.NET. I'm trying to export relational data from
SQL
> Server 2000 to XML with appropriate nesting.
>
> I have included sample code below. Using a Northwind example for clarity,
> the XML output is not nested - Customers are presented first, and then
> Orders are presented at the same level as Customers. I want the Orders
> nested in the correct Customers.
>
> Is there a way to do this using .NET?
>
> Thanks
> Bill
>
> [CODE]
>
> Sub ExportToXML()
>         Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
> Customers", SqlConnection1)
>         Dim orderDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
> Orders", SqlConnection1)
>         SqlConnection1.Open()
>         Dim custDS As DataSet = New DataSet
>         custDA.Fill(custDS, "Customers")
>         orderDA.Fill(custDS, "Orders")
>         Dim custOrderRel As DataRelation =
> custDS.Relations.Add("CustOrders", _
>
> custDS.Tables("Customers").Columns("CustomerID"), _
>
> custDS.Tables("Orders").Columns("CustomerID"))
>         custDS.WriteXml("c:\data\Output.xml")
>     End Sub
>
> [OUTPUT]
>     <?xml version="1.0" standalone="yes" ?>
>     <NewDataSet>
>          <Customers>
>               <CustomerID>ALFKI</CustomerID>
>               <CompanyName>Alfreds Futterkiste</CompanyName>
>                 ...
>          </Customers>
>          <Orders>
>               <OrderID>10643</OrderID>
>               <CustomerID>ALFKI</CustomerID>
>                 ...
>          </Orders>
>     </NewDataSet>
>
>
> [DESIRED OUTPUT]
>     <?xml version="1.0" standalone="yes" ?>
>     <NewDataSet>
>          <Customers>
>               <CustomerID>ALFKI</CustomerID>
>               <CompanyName>Alfreds Futterkiste</CompanyName>
>                 ...
>               <Orders>
>                   <OrderID>10643</OrderID>
>                   <CustomerID>ALFKI</CustomerID>
>                    ...
>              </Orders>
>         </Customers>
>     </NewDataSet>
>
>
>
>
>
>
>
>
>