I want to generate the xml file in the below format through the stored procedure for a database. I am facing few issues to fetch the values in the same format for the foreign key tag which is below.so can any 1 help me out to create a stored procedure which yields the result in below given format.
<?xml version="1.0" encoding="utf-8"?>
<metadata>
<database name="Northwind" default="True">
<schema name="dbo" default="True">
<table name="Categories">
<field name="CategoryID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" />
<field name="CategoryName" type_name="nvarchar" type="String" size="15" precision="255" scale="255" nullable="False" />
<field name="Description" type_name="ntext" type="String" size="1073741823" precision="255" scale="255" />
<field name="Picture" type_name="image" type="Binary" size="2147483647" precision="255" scale="255" />
</table>
<table name="Customers">
<field name="CustomerID" primary_key="True" type_name="nchar" type="String" size="5" precision="255" scale="255" nullable="False" />
<field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" />
<field name="ContactName" type_name="nvarchar" type="String" size="30" precision="255" scale="255" />
<field name="ContactTitle" type_name="nvarchar" type="String" size="30" precision="255" scale="255" />
<field name="Address" type_name="nvarchar" type="String" size="60" precision="255" scale="255" />
<field name="City" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="Region" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="PostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" />
<field name="Country" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" />
<field name="Fax" type_name="nvarchar" type="String" size="24" precision="255" scale="255" />
</table>
<table name="Shippers">
<field name="ShipperID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" />
<field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" />
<field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" />
</table>
<table name="Suppliers">
<field name="SupplierID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" />
<field name="CompanyName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" />
<field name="ContactName" type_name="nvarchar" type="String" size="30" precision="255" scale="255" />
<field name="ContactTitle" type_name="nvarchar" type="String" size="30" precision="255" scale="255" />
<field name="Address" type_name="nvarchar" type="String" size="60" precision="255" scale="255" />
<field name="City" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="Region" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="PostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" />
<field name="Country" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="Phone" type_name="nvarchar" type="String" size="24" precision="255" scale="255" />
<field name="Fax" type_name="nvarchar" type="String" size="24" precision="255" scale="255" />
<field name="HomePage" type_name="ntext" type="String" size="1073741823" precision="255" scale="255" />
</table>
<table name="Orders">
<field name="OrderID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" />
<field name="CustomerID" type_name="nchar" type="String" size="5" precision="255" scale="255" />
<field name="EmployeeID" type_name="int" type="Int32" size="4" precision="10" scale="255" />
<field name="OrderDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" />
<field name="RequiredDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" />
<field name="ShippedDate" type_name="datetime" type="DateTime" size="8" precision="23" scale="3" />
<field name="ShipVia" type_name="int" type="Int32" size="4" precision="10" scale="255" />
<field name="Freight" type_name="money" type="Decimal" size="8" precision="19" scale="255" />
<field name="ShipName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" />
<field name="ShipAddress" type_name="nvarchar" type="String" size="60" precision="255" scale="255" />
<field name="ShipCity" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="ShipRegion" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<field name="ShipPostalCode" type_name="nvarchar" type="String" size="10" precision="255" scale="255" />
<field name="ShipCountry" type_name="nvarchar" type="String" size="15" precision="255" scale="255" />
<foreignkey>
<referenced_object>
<name>Customers</name>
<name>dbo</name>
<name>Northwind</name>
</referenced_object>
<referencing_field name="CustomerID" referenced_field="CustomerID" />
<referencing_cardinality>Many</referencing_cardinality>
<referenced_cardinality>One</referenced_cardinality>
</foreignkey>
<foreignkey>
<referenced_object>
<name>Shippers</name>
<name>dbo</name>
<name>Northwind</name>
</referenced_object>
<referencing_field name="ShipVia" referenced_field="ShipperID" />
<referencing_cardinality>Many</referencing_cardinality>
<referenced_cardinality>One</referenced_cardinality>
</foreignkey>
<foreignkey>
<referenced_object>
<name>Employees</name>
<name>dbo</name>
<name>Northwind</name>
</referenced_object>
<referencing_field name="EmployeeID" referenced_field="EmployeeID" />
<referencing_cardinality>Many</referencing_cardinality>
<referenced_cardinality>One</referenced_cardinality>
</foreignkey>
</table>
<table name="Products">
<field name="ProductID" primary_key="True" type_name="int" type="Int32" size="4" precision="10" scale="255" nullable="False" readonly="True" />
<field name="ProductName" type_name="nvarchar" type="String" size="40" precision="255" scale="255" nullable="False" />
<field name="SupplierID" type_name="int" type="Int32" size="4" precision="10" scale="255" />
<field name="CategoryID" type_name="int" type="Int32" size="4" precision="10" scale="255" />
<field name="QuantityPerUnit" type_name="nvarchar" type="String" size="20" precision="255" scale="255" />
<field name="UnitPrice" type_name="money" type="Decimal" size="8" precision="19" scale="255" />
<field name="UnitsInStock" type_name="smallint" type="Int16" size="2" precision="5" scale="255" />
<field name="UnitsOnOrder" type_name="smallint" type="Int16" size="2" precision="5" scale="255" />
<field name="ReorderLevel" type_name="smallint" type="Int16" size="2" precision="5" scale="255" />
<field name="Discontinued" type_name="bit" type="Boolean" size="1" precision="255" scale="255" nullable="False" />
<foreignkey>
<referenced_object>
<name>Categories</name>
<name>dbo</name>
<name>Northwind</name>
</referenced_object>
<referencing_field name="CategoryID" referenced_field="CategoryID" />
<referencing_cardinality>Many</referencing_cardinality>
<referenced_cardinality>One</referenced_cardinality>
</foreignkey>
<foreignkey>
<referenced_object>
<name>Suppliers</name>
<name>dbo</name>
<name>Northwind</name>
</referenced_object>
<referencing_field name="SupplierID" referenced_field="SupplierID" />
<referencing_cardinality>Many</referencing_cardinality>
<referenced_cardinality>One</referenced_cardinality>
</foreignkey>
</table>
</schema>
</database>
</metadata>
No comments:
Post a Comment