query design for XML from hierarchy table

query design for XML from hierarchy table

Post by howard dierkin » Wed, 20 Mar 2002 15:50:06



I have an existing db table for which I would like to generate an XML
representation.  The table is already hierarchcal in nature ...

PK [guid]
parent [guid]
...data...

it would seem as though I'm almost there (to the "universal table") simply
by the design of the table.  However, SQL Svr books online seems to suggest
that each nested element should have a different tag number, and the way I
understand it, the tag number ties directly to an element.  Therefore, how
do I go about building the query for a proper universal table format???

I'm to the point now, to where I can return a more flat XML representation
and simply transform it via XSLT upon receipt.  I would like to take more
advantage of SQL Svrs XML functionality, however, if I can.

Thanks,

_howard

 
 
 

query design for XML from hierarchy table

Post by Gora » Wed, 20 Mar 2002 16:01:50


Howard,

IMO, I think you should stick with the current implementation. Essentially,
to do what's required with FOR XML EXPLICIT, you'd need to flatten the
hierarchy in the self-referencing table in T-SQL (note in the BOL example
that you have to have every element/attribute in the resulting XML as a
column in the T-SQL query). You can eventually do that with T-SQL, but only
to a certain (hard-coded) level.


Quote:> I have an existing db table for which I would like to generate an XML
> representation.  The table is already hierarchcal in nature ...

> PK [guid]
> parent [guid]
> ...data...

> it would seem as though I'm almost there (to the "universal table") simply
> by the design of the table.  However, SQL Svr books online seems to
suggest
> that each nested element should have a different tag number, and the way I
> understand it, the tag number ties directly to an element.  Therefore, how
> do I go about building the query for a proper universal table format???

> I'm to the point now, to where I can return a more flat XML representation
> and simply transform it via XSLT upon receipt.  I would like to take more
> advantage of SQL Svrs XML functionality, however, if I can.

> Thanks,

> _howard


 
 
 

query design for XML from hierarchy table

Post by Drew Mink » Wed, 27 Mar 2002 20:55:33


Thanks for the great response Goran!

The "elephant ear" hierarchy can be handled in the latest release of SQLXML
3.0 tools,  using the sql:max-depth attribute to an annotated schema.  
Please consult the "Specifying Depth in Recursive Relationships by Using
sql:max-depth" in its documentation for more details.  

Howard, thank you for your post and I hope you found the information
helpful.

Drew Minkin, MCDBA, MCSD, MCSE
SQL Support Professional, Microsoft Corporation

This posting is provide "AS IS" with no warranties, and confers no rights.  
Please do not send email directly to this alias.  Respond to the newsgroup

For the latest in SQLXML, check out
http://www.microsoft.com/sql/techinfo/xml

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

1. SELECT Query to present XML Hierarchy

I can write simple SQL queries using JOINS to query multiple tables and
return that data using FOR XML AUTO, ELEMENTS.  What I would like to do is
also return the child-parent relationship that some tables have within the
XML in place of a "flat" xml file.

For instance, consider the following:

<customers>
    <name/>
    <address/>
    <zip/>
    <orders/>
</customers>

What I really want to do here is have the orders node be a child of
customers and have that data be pulled from the orders table.  I want my XML
returned like this:

<customers>
    <name/>
    <address/>
    <zip/>
    <orders>
        <order/>
        <order/>
    </orders>
</customers>

What kind of SQL query do I need to use to gain this sort of functionality
within SQL 2000?  I've played around with JOINS and that gives me a flat
representation of the data.

Thanks!

Jason

2. More little message diagrams I know nothing about ie. Red Lightning

3. Table Design: Hierarchy

4. Experiences with the Context option

5. hierarchy table design question

6. pgsql/src/backend/utils/mmgr (mcxt.c)

7. Single Table Different XML Hierarchy

8. Security Patch Q298012

9. Query Table Privilege Drill Through Role Hierarchy

10. XML query Design Question

11. XML query node problem: same level root from output xml query

12. Broken XML when running queries with FOR XML AUTO and FOR XML EXPLICIT

13. DESIGN: Should SQL table design mirror OO design?