Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71

    Exclamation Unanswered: Working with Trees and XML...

    Hi, for a new project i'm trying to build a tree structure in SQL using one table with 'Node' & 'ParentNode' fields along with 'title', etc.

    Table = Tree
    Node : ParentNode : Title : Show_Record
    1 0 Root 1
    2 1 Child 1


    Then i'm trying to get SQL to return that in XML to my Tree Control 'oBout ASP TreeView'.

    Now the tree control can accept XML fine as long as it's in a set format, which shouldn't be difficult and should cut my code from 200 lines to one.

    However getting SQL to return the table records in XML is proving to be a total nightmare.

    I've hunted the web but not getting very far, I've even got a couple of O'Reilly guides but still no luck, so any help would be excellent with this.

    I wrote a sql query (basic 'select * from tree for xml raw') which returns the results in RAW XML, but when I run this in Query Analyser it returns the results as one long string broken up with '<' & '>' but gets to the third record and cuts off halfway.

    <row node="1" parentnode="0" title="Root" type_image="book.gif" type_expanded="True"/><row node="2" parentnode="1" title="Service Delivery" type_image="page.gif" type_expanded="False"/><row node="3" parentnode="1" title="Business Support" type_image="page.

    Anyone know why Query Analyser does that?

    Any help in this much appreciated, as you can imagine i'm at my wits end.

    Last edited by sgmuse; 10-26-05 at 10:38.

  2. #2
    Join Date
    May 2002
    Posts
    299
    it's by design. You can set QA's column width (upto 8000) under the Options menu.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    thought it was something like that, thanks.

    any ideas on the query code?

  4. #4
    Join Date
    May 2002
    Posts
    299
    Itzik has written up some very good articles on hierachy. If you have sqlmag account, I suggest you go there for the articles and sample codes.

    http://sqlmag.com
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    now i am so happy i signed up to the SQL Magazine Master CD, just found an article called XML Trees, looks extactly like what i'm after.

    Brilliant, thanks for the help, I needed it.

    all the best,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •