XML hierarchy structure doesnt reflect database hierarchy structure

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML hierarchy structure doesnt reflect database hierarchy structure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-14 : 10:52:03

Cliff writes "IN SQL Server 2000, when using the FOR XML AUTO clause, and joining more than one table in a query, each joined table is displayed in the xml output as the child of the joined table above it, even though in the database both joined tables are siblings and children of the table to which they are joined. The query is seen below. Try it out in the query analyzer.

Use pubs
Select stores.stor_name, sales.ord_date, discounts.discounttype
from stores
join sales on (stores.stor_id = sales.stor_id)
join discounts on (stores.stor_id = discounts.stor_id)
where stor_name = 'Bookbeat'
for xml auto

The output is seen below, where the discounts table data is displayed as the children of sales. If I reverse the order of the joins, then sales data become the children of discounts.

<stores stor_name="Bookbeat">
<sales ord_date="1994-09-14T00:00:00">
<discounts discounttype="Customer Discount"/>
<discounts discounttype="Customer Discount"/>
</sales>
<sales ord_date="1993-03-11T00:00:00">
<discounts discounttype="Customer Discount"/>
</sales>
<sales ord_date="1993-05-22T00:00:00">
<discounts discounttype="Customer Discount"/>
</sales>
</stores>

It seems to me since stores is the parent (1 to many) of both sales and discounts tables, that the xml should look like this:

<stores stor_name="Bookbeat">
<sales ord_date="1994-09-14T00:00:00"/>
<sales ord_date="1993-03-11T00:00:00"/
<sales ord_date="1993-05-22T00:00:00"/>
<discounts discounttype="Customer Discount"/>
<discounts discounttype="Customer Discount"/>
<discounts discounttype="Customer Discount"/>
<discounts discounttype="Customer Discount"/>
</stores>

Any ideas on how to remedy this?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-15 : 13:36:25

Look at the OPENXML and FOR XML entries in Books Online again. There is a special format you use in the FOR XML clause to designate the hierarchy, I don't know if it works with AUTO though. They have an example too, but for the life of me I can't find it. I KNOW that it exists, just be patient and read ALL XML entries thoroughly.

kmarshba
Starting Member

24 Posts

Posted - 2001-12-15 : 23:36:06

Although not specifically the hierarchy you specified, try using the ELEMENTS option.

FOR XML AUTO, ELEMENTS

As far as I remember it's a more logical hierarchy...in theory since I haven't tested it myself because SQL Server is hozed on my machine right now...gotta love those MS security patches

HTH

cburton
Starting Member

1 Post

Posted - 2001-12-19 : 12:43:48

Note to kmarshba: Adding the keyword "elements" to the xml clause doesnt help. Its the same problem, it just displays columns as elements rather than attributes.

mfemenel
Professor Frink

1421 Posts

Posted - 2001-12-19 : 12:48:39

Ah, I see Mr. Volk has recruited another employee to SQL Team. Dropping our production another 20%

Mike
"A program is a device used to convert data into error messages."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 13:32:40

Oh yeah, like YOU'VE done any real work these last several weeks! How's that exam studying going Mike?

Besides, if Kevin is busy on SQL Team, it keeps him away from our production databases.

mfemenel
Professor Frink

1421 Posts

Posted - 2001-12-19 : 14:50:47

I'm living proof of be careful what you wish for. Sometimes you end up with TOO MUCH time on your hands....

Mike
"A program is a device used to convert data into error messages."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-03 : 19:06:09

Does this help?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11545

The "!" portion of the column alias was what I alluded to in my earlier post, I found it in Books Online under "EXPLICIT Mode". Sorry I didn't get that for you before, I couldn't find the damn thing!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources