title: How to show table relationships in xml schema derived from tsql query?
body: <p>I found this tsql query that shows the xml schema of a table and it works very well.</p>
  
  <pre><code>SELECT * FROM TableA
  FOR XML AUTO, XMLSCHEMA
  </code></pre>
  
  <p>I am now attempting to show the relationship between tables via this query.  I am not sure if this is possible.  I have mocked up several test tables and I have not had any luck.  Basically, I am creating a one to many relationship between tableA and tableB, then in the xml schema it would show tableA as a parent and tableB would be a child within the schema.</p>
  
  <p>This is what I have been running. :</p>
  
  <pre><code>SELECT * FROM TableA,
  dbo.TableB 
  FOR XML AUTO, XMLSCHEMA
  </code></pre>
  
  <p>Here is an example xml schema file that I am getting from the above query. </p>
  
  <pre><code>&lt;xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet7" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet7" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified"&gt;
    &lt;xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /&gt;
    &lt;xsd:element name="TableA"&gt;
      &lt;xsd:complexType&gt;
        &lt;xsd:sequence&gt;
          &lt;xsd:element name="columnA" type="sqltypes:int" /&gt;
          &lt;xsd:element name="columnB"&gt;
            &lt;xsd:simpleType&gt;
              &lt;xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="BinarySort"&gt;
                &lt;xsd:maxLength value="50" /&gt;
              &lt;/xsd:restriction&gt;
            &lt;/xsd:simpleType&gt;
          &lt;/xsd:element&gt;
          &lt;xsd:element ref="schema:dbo.tylersTestTable2" minOccurs="0" maxOccurs="unbounded" /&gt;
        &lt;/xsd:sequence&gt;
      &lt;/xsd:complexType&gt;
    &lt;/xsd:element&gt;
    &lt;xsd:element name="TableB"&gt;
      &lt;xsd:complexType&gt;
        &lt;xsd:sequence&gt;
          &lt;xsd:element name="columnA" type="sqltypes:int" /&gt;
          &lt;xsd:element name="columnB"&gt;
            &lt;xsd:simpleType&gt;
              &lt;xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="BinarySort"&gt;
                &lt;xsd:maxLength value="50" /&gt;
              &lt;/xsd:restriction&gt;
            &lt;/xsd:simpleType&gt;
          &lt;/xsd:element&gt;
          &lt;xsd:element name="fkToTableA" type="sqltypes:int" /&gt;
        &lt;/xsd:sequence&gt;
      &lt;/xsd:complexType&gt;
    &lt;/xsd:element&gt;
  &lt;/xsd:schema&gt;
  </code></pre>
  
  <p>As you can see it is displaying the tables as equal levels within the schema and not really displaying a parent child relationship.
  I am kind of at a loss here as I am no dba, but I have to do this for a ton of tables and I don't want to have to write them all by hand.</p>
  
  <p>Any ideas on this one?</p>