Question: What I wish to do on top of exporting to xml as in this thread [SQL DB to XML](https://stackoverflow.com/questions/3952469/export-xml-from-sql-server) is that for every row that is a foreign key, I also want to include the xml represenation of that reference, too.

For example a table Employee:

```
EID(PK)   Name  Supervisor(FK)  
  1       John       NULL  
  2       Kevin       1

```

Should output an XML as:

```
<Employees>  
    <Employee> 
       <EID>1</EID>  
       <Name>John</Name>
       <Supervisor>NULL</Supervisor>
    </Employee>
    <Employee>
       <EID>2</EID>
       <Name>Kevin</Name>
       <Supervisor>
            <EID>1</EID>
            <Name>John</Name>
               <Supervisor>NULL</Supervisor>
       </Supervisor>
    </Employee>
</Employees>

```

I am looking for a solution either in C# (if I can find a way to know a DataColumn is a foreign key), or SSIS.

Answer: 
Try this code, [also on SQL Fiddle](http://sqlfiddle.com/#!3/e0730/8). By specifying the optional `XSINIL` parameter on the `ELEMENTS` directive, you can request that an element also be created for the `NULL` value. In this case, an element that has the xsi:nil attribute set to TRUE is returned for each `NULL` column value and a reference to the `xmlns namespace` will be created at the root.

```
DECLARE @Employee TABLE([EID] INT, [Name] NVARCHAR(5), [Supervisor] INT);

INSERT INTO @Employee
    ([EID], [Name], [Supervisor])
VALUES
    (1, 'John', NULL),
    (2, 'Kevin', 1),
    (3, 'Paul', 1),
    (4, 'Joe', 2);

SELECT T1.EID, T1.[Name],
    (SELECT T2.EID, T2.[Name]
       FROM @Employee T2
      WHERE T2.EID = T1.Supervisor
        FOR XML PATH('Supervisor'),TYPE)
  FROM @Employee T1
   FOR XML PATH('Employee'), ROOT ('Employees'), Elements XSINIL

```

Returns:

```
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Employee>
    <EID>1</EID>
    <Name>John</Name>
  </Employee>
  <Employee>
    <EID>2</EID>
    <Name>Kevin</Name>
    <Supervisor>
      <EID>1</EID>
      <Name>John</Name>
    </Supervisor>
  </Employee>
  <Employee>
    <EID>3</EID>
    <Name>Paul</Name>
    <Supervisor>
      <EID>1</EID>
      <Name>John</Name>
    </Supervisor>
  </Employee>
  <Employee>
    <EID>4</EID>
    <Name>Joe</Name>
    <Supervisor>
      <EID>2</EID>
      <Name>Kevin</Name>
    </Supervisor>
  </Employee>
</Employees>

```
I build a table that combines the KEY\_COLUMN\_USAGE and REFERENTIAL\_CONSTRAINTS to trace from a foreign key (dependent column) to a primary key (principal column), and then when I want to trace the reference, I use this table for look up.

I cannot share the code, but this is a simple query.