SQL2k8 T-SQL: Output into XML file

Question

I have two tables

Table Name: Graph

UID1   UID2
----------- 
12     23 
12     32
41     51
32     41

Table Name: Profiles

NodeID UID  Name
-----------------
1      12   Robs
2      23   Jones
3      32   Lim
4      41   Teo
5      51   Zacks

I want to get an xml file like this:

<graph directed="0">
  <node id="1">
    <att name="UID"  value="12"/>
    <att name="Name" value="Robs"/>
  </node>
  <node id="2">
    <att name="UID" value="23"/>
    <att name="Name" value="Jones"/>
  </node>
  <node id="3">
    <att name="UID" value="32"/>
    <att name="Name" value="Lim"/>
  </node>
  <node id="4">
    <att name="UID" value="41"/>
    <att name="Name" value="Teo"/>
  </node>
  <node id="5">
    <att name="UID" value="51"/>
    <att name="Name" value="Zacks"/>
  </node>
  <edge source="12" target="23" /> 
  <edge source="12" target="32" /> 
  <edge source="41" target="51" /> 
  <edge source="32" target="41" /> 
</graph>

Thanks very much!

Answer 1

You can use a union to combine different nodes. This does get quite complicated:

select  p.nodeid as 'node/@id'
,       (
        select  [@name], [@value]
        from    (
                select  'UID' as '@name'
                ,       cast(uid as varchar(10)) as '@value'
                ,       nodeid
                from    @profiles
                union all
                select  'Name' as '@name'
                ,       name as '@value'
                ,       nodeid
                from    @profiles
                ) sub
        where   sub.nodeid = p.nodeid
        for xml path('att'), type
        ) as node
,       null as 'edge/@source'
,       null as 'edge/@target'
from    @profiles p
union all
select  null as 'node/@node'
,       null as node
,       g.uid1 as 'edge/@source'
,       g.uid2 as 'edge/@target'
from    @graph g
for xml path(''), root('graph'), type

Test data:

declare @graph table (uid1 int, uid2 int)
declare @profiles table (nodeid int, uid int, name varchar(25))

insert into @graph values (12, 23), (12,32), (41,51), (32,41)
insert into @profiles values (1,12,'Robs'), (2,23,'Jones'), (3,32,'Lim'), 
    (4,41,'Teo'), (5,51,'Zacks')