StrataFrame Forum

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

http://forum.strataframe.net/Topic23475.aspx

By Doron Farber - 6/12/2009

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

By Trent L. Taylor - 6/12/2009

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?
By Doron Farber - 6/12/2009

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


 


 

By Doron Farber - 6/12/2009

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

By Trent L. Taylor - 6/15/2009

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.



MyBO.CurrentDataTable.WriteXml("c:\OutFile.XML", XmlWriteMode.WriteSchema)
By Edhy Rijo - 6/15/2009

Trent L. Taylor (06/15/2009)
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.




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?
By Doron Farber - 6/15/2009

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

By Trent L. Taylor - 6/15/2009

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.
By Doron Farber - 6/15/2009

Hi Trent,

Thanks for your reply.

[/quote]
The XmlBasics class is not going to help you much in this situation.
[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