August 23, 2005 at 2:54 am
Hi guys,
we have some problem in creating the XML from the SQL table.
Our SQL table is something like this:
Menu_ID,Menu_Name, Menu_Level, Parent_Menu_ID, Menu_Sequence
001 Item1 1 0 1
002 Item2 1 0 2
00201 Item2.1 2 002 1
00202 Item2.2 2 002 2
00203 Item2.3 2 002 3
003 Item3 1 0 3
00301 Item3.1 2 003 1
00302 Item3.2 2 003 2
We need a XML in such a way as below:
<Menu>
<Menu_ID>001</Menu_ID>
<Menu_ID>002<MenuTable>
<Sub_Menu>
<Menu_ID> 00201 </Menu_ID>
<Menu_ID> 00202 </Menu_ID>
<Menu_ID> 00203 </Menu_ID>
</MenuTable>
</Sub_Menu>
</Menu_ID>
<Menu_ID>003
<MenuTable>
<Menu_ID> 00301 </Menu_ID>
<Menu_ID> 00302 </Menu_ID>
<Menu_ID> 00303 </Menu_ID>
</MenuTable>
</Menu_ID>
</Menu>
If we generate the above it is giving error message as
"Although the XML document is well formed, it contains structure that data view cannot display.
The table [Menu_Table]cannot be the child to iteself in nested relations."
infact, our table has to return a recursive XML.
Can you please give soultions to prepare a query that will return XML as like above
August 24, 2005 at 3:16 am
The problem is that u have a text node on first menu_ID item, but then have child elements as well
<Menu_ID>002
<MenuTable>
<Sub_Menu>
<Menu_ID> 00201 </Menu_ID>
<Menu_ID> 00202 </Menu_ID>
<Menu_ID> 00203 </Menu_ID>
</MenuTable>
</Sub_Menu>
</Menu_ID>
This isn't good practice when dealing with XML (hence some things not wanting to handle it!), and this value should be moved to an attribute.
<Menu_ID id="002">
<MenuTable>
<Sub_Menu>
<Menu_ID> 00201 </Menu_ID>
<Menu_ID> 00202 </Menu_ID>
<Menu_ID> 00203 </Menu_ID>
</MenuTable>
</Sub_Menu>
</Menu_ID>
So the basic answer to ur question is no, i cant give u a query to return the XML because the way it's structured is incorrect!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply