?
Solved

How to convert hierarchical table to XML

Posted on 2007-10-17
2
Medium Priority
?
226 Views
Last Modified: 2013-11-18
I have a hiearchically structured table in SQL Server and need to convert it to XML/XSLT zones too since it's related. It would be nice to do this in SQL Server and return the XML string though.  I am reducing the complexity of the example some.   Here is an example table that hierarchically represents states, counties, and cities in Texas and Oklahoma:

ID       OBJECT      REFERENCE   OBJECT_TYPE   OBJECT_DESCRIPTION
------------------------------------------------------------------------------------------------
1        TX              NULL               State                  TX - Texas
2       OK             NULL                State                   OK - Oklahoma
3        HS             1                      County                HS - Harris
4        BZ             1                      County                BZ - Brazoria
5        SC             2                      County                SC - Some County in OK
6        HOU          3                       City                     HOU - Houston
7        PLD           4                       City                     PLD - Pearland
8        OKC          5                       City                     OKC - Oklahoma City
9        ALV          4                       City                     ALV - Alvin

I would like that represented as:
<tree>
       <node type="State value="TX-Texas">
            <childNode type="County" value="HS - Harris">
                     <childNode type="City" value="HOU - Houston"/>
             </childNode>
             <childNode type="County" value="BZ - Brazoria">
                      <childNode type="City" value="PLD - Pearland" />
                      <childNode type="City" value="ALV - Alvin" />
             <childNode>
       </node>
       <node type="State" value="OK-Oklahoma">
            <childNode type="County" value="SC - Some County">
                     <childNode type="City" value="OKC - Oklahoma City"/>
             </childNode>
        </node>
 </tree>


You can see the tree is generic so no matter what type of hiearchy tree (or how deep it goes) I would pass it the same procedure/tree should be used.  

Thanks for the help.

Here is some sql to create this data if it helps:

--===================================================
--Creating table
create table hrch (ID int not null identity, Object varchar(10), Reference int, Object_Type varchar(25), Object_Description varchar(50))
alter table hrch add constraint pkhrch primary key (ID)
alter table hrch add constraint fkhrchself foreign key (Reference) references hrch (ID)

--Inserting data
DBCC CHECKIDENT (hrch, RESEED, 0)
insert into hrch(object, reference, object_type, object_description)
values('TX',NULL,'State','TX - Texas')
insert into hrch(object, reference, object_type, object_description)
values('OK',NULL,'State','OK - Oklahoma')
insert into hrch(object, reference, object_type, object_description)
values('HS',1,'County','HS - Harris')
insert into hrch(object, reference, object_type, object_description)
values('BZ',1,'County','BZ - Brazoria')
insert into hrch(object, reference, object_type, object_description)
values('SC',2,'County','SC - Some County')
insert into hrch(object, reference, object_type, object_description)
values('HOU',3,'City','HOU - Houston')
insert into hrch(object, reference, object_type, object_description)
values('PLD',4,'City','PLD - Pearland')
insert into hrch(object, reference, object_type, object_description)
values('OKC',5,'City','OKC - Oklahoma City')
insert into hrch(object, reference, object_type, object_description)
values('ALV',4,'City','ALV - Alvin')
select * from hrch;


0
Comment
Question by:dentyne
2 Comments
 
LVL 3

Accepted Solution

by:
Askeeto earned 2000 total points
ID: 20104088
This query:

select 1 As Tag, Null as Parent, h1.Object_Type as [node!1!type], h1.Object_Description as [node!1!value]
                               , NULL as [childNode!2!type], NULL as [childNode!2!value]
                               , NULL as [childNode!3!type], NULL as [childNode!3!value]
from hrch h1
where h1.Reference is null
union all
select 2 As Tag, 1 as Parent, h1.Object_Type as [node!1!type], h1.Object_Description as [node!1!value]
                            , h2.Object_Type as [childNode!2!type], h2.Object_Description as [childNode!2!value]
                               , NULL as [childNode!3!type], NULL as [childNode!3!value]
from hrch h1
  inner join hrch h2
  on h1.ID = h2.Reference
where h1.Reference is null
union all
select 3 As Tag, 2 as Parent, h1.Object_Type as [node!1!type], h1.Object_Description as [node!1!value]
                            , h2.Object_Type as [childNode!2!type], h2.Object_Description as [childNode!2!value]
                            , h3.Object_Type as [childNode!2!type], h3.Object_Description as [childNode!2!value]
from hrch h1
  inner join hrch h2
  on h1.ID = h2.Reference
  inner join hrch h3
  on h2.ID = h3.Reference
where h1.Reference is null
--ORDER BY [node!1!type] DESC , [childNode!2!type]
for xml explicit, root('tree')

Results in this xml output:

<tree>
  <node type="State" value="TX - Texas" />
  <node type="State" value="OK - Oklahoma">
    <childNode type="County" value="HS - Harris" />
    <childNode type="County" value="BZ - Brazoria">
      <childNode type="County" value="SC - Some County" />
      <childNode type="City" value="HOU - Houston" />
    </childNode>
  </node>
</tree>
0
 
LVL 1

Author Comment

by:dentyne
ID: 20176625
Thanks!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question