On Mar 9, 4:23=A0am, "Joe Fawcett" <joefawc...@newsgroup.nospam> wrote:
> <emailhe...@gmail.com> wrote in message
>
> news:8329a8e3-b0eb-4f8a-a03c-7a939176c6fa@l......
>
>
>
>
>
> > Hi everyone,
>
> > I need your help getting my getXML method to function the way I need
> > it to. I have a stored procedure that returns data into a dataset (I
> > am using VB.NET). I use getXML with dataset and I get the following
> > result:
>
> > <NewDataSet>
> > <Table>
> > =A0 <StudentFirstName>John</StudentFirstName>
> > =A0 <StudentMiddleName>J</StudentMiddleName>
> > =A0 <StudentLastName>Smith</StudentLastName>
> > =A0 <StudentAddressType>Permanent</StudentAddressType>
> > =A0 <StudentAddressLine1>123 Some Rd</StudentAddressLine1>
> > =A0 <StudentAddressLine2 />
> > =A0 <StudentAddressLine3 />
> > =A0 <StudentAddressLine4 />
> > =A0 <StudentAddressCity>New Town</StudentAddressCity>
> > =A0 <StudentAddressState>NJ</StudentAddressState>
> > =A0 <StudentAddressPostalCode>12345</StudentAddressPostalCode>
> > =A0 <StudentAddressCounty />
> > =A0 <StudentAddressCountry>United States</StudentAddressCountry>
> > =A0 <StudentHomePhoneNumber>1234567890</StudentHomePhoneNumber>
> > =A0 <StudentCellPhoneNumber>1234567890</StudentCellPhoneNumber>
> > =A0 <Parent1FirstName>Jane</Parent1FirstName>
> > =A0 <Parent1MiddleName />
> > =A0 <Parent1LastName>Smith</Parent1LastName>
> > =A0 <Parent1HomePhoneNumber>1234567890</Parent1HomePhoneNumber>
> > =A0 <Parent1WorkPhoneNumber />
> > =A0 <Parent1EmailType>Personal</Parent1EmailType>
> > =A0 <Parent1EmailAddress>j...@mail.com</Parent1EmailAddress>
> > =A0 <Parent2FirstName>John Sr</Parent2FirstName>
> > =A0 <Parent2MiddleName />
> > =A0 <Parent2LastName>Smith</Parent2LastName>
> > =A0 <Parent2HomePhoneNumber>1234567890</Parent2HomePhoneNumber>
> > =A0 <Parent2WorkPhoneNumber />
> > =A0 <Parent2EmailType>Personal</Parent2EmailType>
> > =A0 <Parent2EmailAddress>joh...@mail.com</Parent2EmailAddress>
> > </Table>
> > </NewDataSet>
>
> > Which is all fine and good but what I really need to get is the
> > following:
> > <Data>
> > =A0 <StudentData>
> > =A0 =A0 =A0 <StudentFirstName>John</StudentFirstName>
> > =A0 =A0 =A0 <StudentMiddleName>J</StudentMiddleName>
> > =A0 =A0 =A0 <StudentLastName>Smith</StudentLastName>
> > =A0 =A0 =A0 <StudentAddressType>Permanent</StudentAddressType>
> > =A0 =A0 =A0 <StudentAddressLine1>123 Some Rd</StudentAddressLine1>
> > =A0 =A0 =A0 <StudentAddressLine2 />
> > =A0 =A0 =A0 <StudentAddressLine3 />
> > =A0 =A0 =A0 <StudentAddressLine4 />
> > =A0 =A0 =A0 <StudentAddressCity>New Town</StudentAddressCity>
> > =A0 =A0 =A0 <StudentAddressState>NJ</StudentAddressState>
> > =A0 =A0 =A0 <StudentAddressPostalCode>12345</StudentAddressPostalCode>
> > =A0 =A0 =A0 <StudentAddressCounty />
> > =A0 =A0 =A0 <StudentAddressCountry>United States</StudentAddressCountry=
>
> > =A0 =A0 =A0 <StudentHomePhoneNumber>1234567890</StudentHomePhoneNumber>
> > =A0 =A0 =A0 <StudentCellPhoneNumber>1234567890</StudentCellPhoneNumber>
> > =A0 =A0 =A0 </StudentData>
> > =A0 <ParentData>
> > =A0 =A0 =A0 <Parent>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1FirstName>Jane</Parent1FirstName>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1MiddleName />
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1LastName>Smith</Parent1LastName>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1HomePhoneNumber>1234567890</
> > Parent1HomePhoneNumber>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1WorkPhoneNumber />
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1EmailType>Personal</Parent1EmailTyp=
e>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent1EmailAddress>j...@mail.com</
> > Parent1EmailAddress>
> > =A0 =A0 =A0 </Parent>
> > =A0 =A0 =A0 <Parent>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2FirstName>John Sr</Parent2FirstName=
>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2MiddleName />
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2LastName>Smith</Parent2LastName>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2HomePhoneNumber>1234567890</
> > Parent2HomePhoneNumber>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2WorkPhoneNumber />
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2EmailType>Personal</Parent2EmailTyp=
e>
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 <Parent2EmailAddress>joh...@mail.com</
> > Parent2EmailAddress>
> > =A0 =A0 =A0 </Parent>
> > =A0 </ParentData>
> > </Data>
>
> > Can someone please help me figure out how to impose this structure
> > upon the XML I get from the dataset returned by my stored procedure?
> > Thanks in advance for your anticipated assistance!
>
> I don't often use datasets in this way but I can suggest two options:
> * Don't use a dataset, just use SQL Server's XML methods to return the XM=
L
> directly (this assumes a SQL Server back end)
> * Transform the data after retrieving it from the dataset using XSLT
>
> If either of those suits you let us know which, and what your database is=
 if
> option one, and we'll try to assist.
>
> --
>
> Joe Fawcett (MVP - XML)http://joe.fawcett.name- Hide quoted text -
>
> - Show quoted text -

Thanks Joe!

I am in fact using SQL Server 2005. I would love to know how to handle
this best through the SQL server. If you think XSLT would be a better
option I would love to hear about that as well. I am afraid I am quite
a newbie when it comes to this. Thanks again for your anticipated
assistance!