title: How to generate xml file as output for a database in specific format through stored procedure in SQL Server?
body: <p>I want to generate an xml file in the format shown below through a stored procedure. </p>
  
  <p>I am facing few issues to fetch the values in the same format for the foreign key tag which is below. So can anyone help me out on how to create a stored procedure which yields the result in below given format?</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;metadata&gt;
      &lt;database name="Northwind" default="True"&gt;
          &lt;schema name="dbo" default="True"&gt;
              &lt;table name="Categories"&gt;
                  &lt;field name="CategoryID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" /&gt;
                  &lt;field name="CategoryName" type_name="nvarchar" type="String" size="15" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="Description" type_name="ntext" type="String" size="1073741823" precision="255" scale="255" /&gt;
                  &lt;field name="Picture" type_name="image" type="Binary" size="2147483647" precision="255" scale="255" /&gt;
              &lt;/table&gt;
              &lt;table name="Customers"&gt;
                  &lt;field name="CustomerID" primary_key="True" type_name="nchar" type="String" size="5" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="ContactName" type_name="nvarchar" type="String" size="30" precision="255" scale="255" /&gt;
                  &lt;field name="ContactTitle" type_name="nvarchar" type="String" size="30" precision="255" scale="255" /&gt;
                  &lt;field name="Address" type_name="nvarchar" type="String" size="60" precision="255" scale="255" /&gt;
                  &lt;field name="City" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="Region" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="PostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" /&gt;
                  &lt;field name="Country" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" /&gt;
                  &lt;field name="Fax" type_name="nvarchar" type="String" size="24" precision="255" scale="255" /&gt;
              &lt;/table&gt;
              &lt;table name="Shippers"&gt;
                  &lt;field name="ShipperID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" /&gt;
                  &lt;field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" /&gt;
              &lt;/table&gt;
              &lt;table name="Suppliers"&gt;
                  &lt;field name="SupplierID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" /&gt;
                  &lt;field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="ContactName" type_name="nvarchar" type="String" size="30" precision="255" scale="255" /&gt;
                  &lt;field name="ContactTitle" type_name="nvarchar" type="String" size="30" precision="255" scale="255" /&gt;
                  &lt;field name="Address" type_name="nvarchar" type="String" size="60" precision="255" scale="255" /&gt;
                  &lt;field name="City" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="Region" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="PostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" /&gt;
                  &lt;field name="Country" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" /&gt;
                  &lt;field name="Fax" type_name="nvarchar" type="String" size="24" precision="255" scale="255" /&gt;
                  &lt;field name="HomePage" type_name="ntext" type="String" size="1073741823" precision="255" scale="255" /&gt;
              &lt;/table&gt;
              &lt;table name="Orders"&gt;
                  &lt;field name="OrderID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" /&gt;
                  &lt;field name="CustomerID" type_name="nchar" type="String" size="5" precision="255" scale="255" /&gt;
                  &lt;field name="EmployeeID" type_name="int" type="Int32" size="4" precision="10" scale="255" /&gt;
                  &lt;field name="OrderDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" /&gt;
                  &lt;field name="RequiredDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" /&gt;
                  &lt;field name="ShippedDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" /&gt;
                  &lt;field name="ShipVia" type_name="int" type="Int32" size="4" precision="10" scale="255" /&gt;
                  &lt;field name="Freight" type_name="money" type="Decimal" size="8" precision="19" scale="255" /&gt;
                  &lt;field name="ShipName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" /&gt;
                  &lt;field name="ShipAddress" type_name="nvarchar" type="String" size="60" precision="255" scale="255" /&gt;
                  &lt;field name="ShipCity" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="ShipRegion" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;field name="ShipPostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" /&gt;
                  &lt;field name="ShipCountry" type_name="nvarchar" type="String" size="15" precision="255" scale="255" /&gt;
                  &lt;foreignkey&gt;
                      &lt;referenced_object&gt;
                          &lt;name&gt;Customers&lt;/name&gt;
                          &lt;name&gt;dbo&lt;/name&gt;
                          &lt;name&gt;Northwind&lt;/name&gt;
                      &lt;/referenced_object&gt;
                      &lt;referencing_field name="CustomerID" referenced_field="CustomerID" /&gt;
                      &lt;referencing_cardinality&gt;Many&lt;/referencing_cardinality&gt;
                      &lt;referenced_cardinality&gt;One&lt;/referenced_cardinality&gt;
                  &lt;/foreignkey&gt;
                  &lt;foreignkey&gt;
                      &lt;referenced_object&gt;
                          &lt;name&gt;Shippers&lt;/name&gt;
                          &lt;name&gt;dbo&lt;/name&gt;
                          &lt;name&gt;Northwind&lt;/name&gt;
                      &lt;/referenced_object&gt;
                      &lt;referencing_field name="ShipVia" referenced_field="ShipperID" /&gt;
                      &lt;referencing_cardinality&gt;Many&lt;/referencing_cardinality&gt;
                      &lt;referenced_cardinality&gt;One&lt;/referenced_cardinality&gt;
                  &lt;/foreignkey&gt;
                  &lt;foreignkey&gt;
                      &lt;referenced_object&gt;
                          &lt;name&gt;Employees&lt;/name&gt;
                          &lt;name&gt;dbo&lt;/name&gt;
                          &lt;name&gt;Northwind&lt;/name&gt;
                      &lt;/referenced_object&gt;
                      &lt;referencing_field name="EmployeeID" referenced_field="EmployeeID" /&gt;
                      &lt;referencing_cardinality&gt;Many&lt;/referencing_cardinality&gt;
                      &lt;referenced_cardinality&gt;One&lt;/referenced_cardinality&gt;
                  &lt;/foreignkey&gt;
              &lt;/table&gt;
              &lt;table name="Products"&gt;
                  &lt;field name="ProductID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" /&gt;
                  &lt;field name="ProductName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" /&gt;
                  &lt;field name="SupplierID" type_name="int" type="Int32" size="4" precision="10" scale="255" /&gt;
                  &lt;field name="CategoryID" type_name="int" type="Int32" size="4" precision="10" scale="255" /&gt;
                  &lt;field name="QuantityPerUnit" type_name="nvarchar" type="String" size="20" precision="255" scale="255" /&gt;
                  &lt;field name="UnitPrice" type_name="money" type="Decimal" size="8" precision="19" scale="255" /&gt;
                  &lt;field name="UnitsInStock" type_name="smallint" type="Int16" size="2" precision="5" scale="255" /&gt;
                  &lt;field name="UnitsOnOrder" type_name="smallint" type="Int16" size="2" precision="5" scale="255" /&gt;
                  &lt;field name="ReorderLevel" type_name="smallint" type="Int16" size="2" precision="5" scale="255" /&gt;
                  &lt;field name="Discontinued" type_name="bit" type="Boolean" size="1" precision="255" scale="255" nullable="False" /&gt;
                  &lt;foreignkey&gt;
                      &lt;referenced_object&gt;
                          &lt;name&gt;Categories&lt;/name&gt;
                          &lt;name&gt;dbo&lt;/name&gt;
                          &lt;name&gt;Northwind&lt;/name&gt;
                      &lt;/referenced_object&gt;
                      &lt;referencing_field name="CategoryID" referenced_field="CategoryID" /&gt;
                      &lt;referencing_cardinality&gt;Many&lt;/referencing_cardinality&gt;
                      &lt;referenced_cardinality&gt;One&lt;/referenced_cardinality&gt;
                  &lt;/foreignkey&gt;
                  &lt;foreignkey&gt;
                      &lt;referenced_object&gt;
                          &lt;name&gt;Suppliers&lt;/name&gt;
                          &lt;name&gt;dbo&lt;/name&gt;
                          &lt;name&gt;Northwind&lt;/name&gt;
                      &lt;/referenced_object&gt;
                      &lt;referencing_field name="SupplierID" referenced_field="SupplierID" /&gt;
                      &lt;referencing_cardinality&gt;Many&lt;/referencing_cardinality&gt;
                      &lt;referenced_cardinality&gt;One&lt;/referenced_cardinality&gt;
                  &lt;/foreignkey&gt;
              &lt;/table&gt;
            &lt;/schema&gt;
      &lt;/database&gt;
  &lt;/metadata&gt;
  
  
  
  
  
  
  
  
  
  
  
  Declare @temp Table(result xml)
  DECLARE @value varchar(50)
  insert into @temp values((
   Select DB_NAME() AS '@name',
  (SELECT   sc.SCHEMA_NAME as '@name'  ,
  (SELECT TABLE_NAME as '@name',        
  ( 
   SELECT COLUMN_NAME as '@name',
  
              t.name as '@type_name',
  
               case DATA_TYPE 
                  when 'nvarchar'
                 then 'String' 
                  when 'varchar'  
                  then 'String'
                  when 'int'
                  then 'Int32'
                  when 'bigint'
                  then 'Int64'
                   when 'bit'
                   then 'Boolean'
                   when 'datetime'
                   then 'DateTime'
                  else null 
                  end  as '@type', 
              NUMERIC_PRECISION as '@precision',
              NUMERIC_SCALE as '@scale',
              case data_type 
                  when 'nvarchar'
                 then CHARACTER_MAXIMUM_LENGTH 
                  when 'varchar'  
                  then CHARACTER_MAXIMUM_LENGTH 
                  else null 
              end  as '@size', 
              INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE AS '@nullable',
              'true' as '@readonly',
              'true'as '@primary_key'  
  
      FROM INFORMATION_SCHEMA.COLUMNS 
      INNER JOIN 
      sys.types t ON TYPE_ID(INFORMATION_SCHEMA.COLUMNS.DATA_TYPE) = t.user_type_id
  LEFT OUTER JOIN 
  sys.index_columns ic ON t.name=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
  --ON ic.column_id = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
  LEFT OUTER JOIN 
      sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
  
      where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 
          INFORMATION_SCHEMA.TABLES.TABLE_NAME 
      order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION 
      For XML PATH ('field'), type 
  ) 
  
  FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_SCHEMA='my schema' 
  ORDER BY TABLE_NAME ASC  
  For XML PATH ('table'),type) 
  from INFORMATION_SCHEMA.SCHEMATA sc where sc.SCHEMA_NAME IN(Select distinct TOP 1 ta.TABLE_SCHEMA from
  INFORMATION_SCHEMA.TABLES ta )
  For XML PATH('schema'),type)
  For XML PATH('database'),Root('metadata')))
  select * from @temp
  </code></pre>
  
  <p>The above code is used to generate just table data</p>
  
  <p>The below stored procedure is used to generate the foreign key constraint</p>
  
  <pre><code>(select INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME as 'name',
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as 'name',
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_CATALOG as 'name' , 
  referencing_field_name =FK_COLS.COLUMN_NAME,  
  referenced_field = PK_COLS.COLUMN_NAME,
  referencing_cardinality='Many',
  referenced_cardinality='one'
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST 
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  ON REF_CONST.CONSTRAINT_CATALOG = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_CATALOG
  
  AND REF_CONST.CONSTRAINT_SCHEMA = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA
  AND REF_CONST.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
  AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'
  
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
  ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
  AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
  AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
  AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
  
  FOR XML RAW ('referenced_object'),  ELEMENTS, ROOT ('foreign key'))
  </code></pre>
  
  <p>We are facing an issue in integrating these two stored procedures</p>
  
  <p>So, Is there any way to generate the xml file in the same format dynamically </p>