StrataFrame Forum » StrataFrame Application Framework - V1 » WebForms (How do I?) » What's the best a way to convert ms sql 2008 data to XML files?

What's the best a way to convert ms sql 2008 data to XML files?

Doron Farber — Mon, 15 Jun 2009 12:45:12 GMT

Hi Trent,

Say I have several tables related to each other and I want to create the hierarchy of the data such as parent child within the XML.

These are small tables where I store the data of menu options and sub options etc...
Then I use the XML file for the web site to display all options etc... I need to select only certain fields that hold the data for the menus. I have some other fields that are not needed for the XML.

I appreciate any feedback.

Thanks,

Doron

RE: What's the best a way to convert ms sql 2008 data to XML files?

Doron Farber — Mon, 15 Jun 2009 12:45:12 GMT

Hi Trent,

Thanks for your reply.

[quote][/quote]
The XmlBasics class is not going to help you much in this situation.
[quote][/quote]

Maybe that could an enhancement feature to the XMLBasic class, to have the capability of more then one table added into the XML!!

Regards,

Doron

RE: What's the best a way to convert ms sql 2008 data to XML files?

Trent L. Taylor — Mon, 15 Jun 2009 12:06:38 GMT

You can still do this through ADO.NET. All you need to do is create a DataSet object, then add the the data tables into the data set. You will most likely want to create a clone or copy of the table within the BO otherwise you can have other issues. This will really be the only way to go about this. The XmlBasics class is not going to help you much in this situation.

RE: What's the best a way to convert ms sql 2008 data to XML files?

Doron Farber — Mon, 15 Jun 2009 08:46:19 GMT

Hi Trent,

As Edhy indicated below there will be 3 tables involved. Parent, Child and Grand Child. So the hierarchy should be created based on that as well.
How would you add addtional tables to the XML and reflect the relations there as well?

Thanks,

Doron

RE: What's the best a way to convert ms sql 2008 data to XML files?

Edhy Rijo — Mon, 15 Jun 2009 08:21:22 GMT

[quote][b]Trent L. Taylor (06/15/2009)[/b][hr]You can just use the ADO.NET to output to XML. If you take the current data table you can output it directly to XML.[/quote]

This will create the XML file for that single table. What Doron is asking for is to create an XML file from 3 or more BO related each other as Parent/Child/GrandChild. I think that this could be done by manually enumerating each BO and using the MicroFour XMLBasics class to create the XML file with the relation data, or is there a way to pass all BOs to automatically create such an XML file?

RE: What's the best a way to convert ms sql 2008 data to XML files?

Trent L. Taylor — Mon, 15 Jun 2009 06:53:16 GMT

You can just use the ADO.NET to output to XML. If you take the current data table you can output it directly to XML.

[codesnippet]MyBO.CurrentDataTable.WriteXml("c:\OutFile.XML", XmlWriteMode.WriteSchema)[/codesnippet]

RE: What's the best a way to convert ms sql 2008 data to XML files?

Doron Farber — Fri, 12 Jun 2009 20:23:34 GMT

Hi Trent,

To give you an idea, I need to generate an XML file similar to the Messages_1033.XML created by the Localization and Messaging Editor.

So to clarify my question, is there a method somewhere in SF that will help me generating that type of xml output?

Thanks,

Doron

RE: What's the best a way to convert ms sql 2008 data to XML files?

Doron Farber — Fri, 12 Jun 2009 09:58:52 GMT

Hi Trent,

I need to see the data in the XML and not onky the tables stucture only. I need to have the actual data with the right hierarchy based on the relations.. Say the data in the SearchCategory will hold the data for some tabs. The data in the SearchOption will present different options to bring the tabs. In the below example I have 2 options such as People Search and Email Search. Each option will have several tabs as shown in the SearchCategory etc...

SearchOptions table:

PK SearchOption
1   People Search
2   Email Search

SearchCategory Table:

PK FK SearchCategory
1   1 Name
2   1 Address
3   1 Phone
4   1 SSN
5   2 Reverse Email
6   2 Phone Search

Thanks,

Doron

RE: What's the best a way to convert ms sql 2008 data to XML files?

Trent L. Taylor — Fri, 12 Jun 2009 09:43:23 GMT

So are you want to retrieve a query from SQL Server then save that data to an XML file or are you wanting to create a structures table in XML that represents your structures in SQL?