Nested Table Relations in SQLXML

Hi Folks

This question applies to SQL Server 2000 and SQLXML.

I have a table with columns as follows:

AccountID ~ ParentAccountID ~ AccountData

Sample data:

1 ~ NULL ~ This is a parent account

2 ~ 1 ~ This is a child account

As you can see, there is an inherant tree structure in the above data: ParentAccountID is a foreign key to Primary Key AccountID.

I wish to query these using a FOR XML and retrieve a document like the following:

<account>

<accountid>1</accountid>

<accountdata>I am a parent account</accountdata>

<account>

<accountid>1</accountid>

<accountdata>I am a child account</accountdata>

</account>

</account>

Is this possible and if so what would be the sql?

Many thanks

Iain A. Mcleod

In SQL Server 2000 this is not directly doable. You should just use a flat result (using FOR XML AUTO) and then use a mid-tier XSLT transform.

In SQL Server 2005, you can use nested user defined functions as described in http://msdn.microsoft.com/library/en-us/dnsql90/html/forxml2k5.asp

Best regards

|||

Many thanks for your reply Michael.

I had separately come to the same conclusion having begun to discover that sql server 2000's xml capabilities were a bit of a hacked add on. I ended up doing it by pulling out datasets and directly mapping the fields to my business objects.

Not great but at least I avoided the need to install sql xml libraries on our webserver... The less one has to ask of our systems people the better as far as I'm concerned :-)

Regards

Iain