How to convert hierarchical table to XML

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;


LVL 1
dentyneAsked:
Who is Participating?
 
AskeetoConnect With a Mentor Commented:
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
 
dentyneAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.