Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-01 : 08:23:28
|
Ronald writes "I'm busy developing a flash tree component which should make use of an XML stream/file as input. To do this, I built an SQL table within SQL Server consisting of the fields "user_id", "node_id", "parent_id" and "description". Here's an example of the XML schema I want to use: <folder nodeId="2" nodeName="Economics" parentNodeId="1" groupId="0"> <node nodeId="3" nodeName="Class 1" parentNodeId="2" groupId="1"/> <node nodeId="4" nodeName="Class 2" parentNodeId="2" groupId="2"/> <node nodeId="5" nodeName="Class 3" parentNodeId="2" groupId="3"/> <node nodeId="6" nodeName="Class 4" parentNodeId="2" groupId="4"/> </folder> Where "nodename" is the node description, "parentnodeid" is the parent_id and "nodeId" and "groupid" are the node_id. So basically I need the SQL query that will output the correct order for the nodes. Do you have an idea on how to implement that?" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-01 : 12:54:51
|
| Have you looked at FOR XML AUTO in Books Online? You would need to put the columns in the correct order in the SELECT statement.Tara |
 |
|
|
Ronald
Starting Member
1 Post |
Posted - 2003-10-02 : 08:43:22
|
| Yes, but the problem is that i also need to formulate an SQL Query for MySQL. So the FOR XML AUTO function is not an option, at least not for MySQL. Do you have other suggestions?Thanks in Advance!Ronald |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-02 : 12:30:21
|
| Can't help you with MySql. BTW, this is a SQL Server forum, not many people here have much MySql knowledge. You could try the "Other" forum here though.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-02 : 13:26:34
|
| Two suggestions:1. Write separate queries for SQL Server and MySQL. This will give you the best performance for each product's capabilities. Trying to write generic SQL that works with all products inevitably leads to performance issues in ALL of them. However, if you're dealing with a small amount of data then it's probably not so bad. If you absolutely have to use generic SQL:2. Write your XML-generating routine in a separate application layer, and use generic SQL for the data ONLY. The app would then have a consistent set of data to work from, and can do all of the ordering and converting itself. |
|
|
|
|
|
|
SQL Query to build XML output