asked
I want to merge two queries to get it as a xml result in specific format
Hi i want to get a stored procedure result in a specific format i have tried it in two different queries but i'am facing difficulty in merging those two to get it in that format.I have posted my queries and XML format as well.Please any one help me out. --XML format -- ForeignkeyTablename Schemaname Databasename Many One --fist query-- 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' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS for xml raw('referenced_object'),elements --second query-- select 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(''),elements
Rewrite the 2 queries into one (and handle the nulll values as you need), and then convert to XML select T.TABLE_NAME as 'name', T.TABLE_SCHEMA as 'name', T.TABLE_CATALOG as 'name', referencing_field_name = FK_COLS.COLUMN_NAME, referenced_field = PK_COLS.COLUMN_NAME, referencing_cardinality='Many', --need to work out what to use here instead of a scalar value referenced_cardinality='one' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST on REF_CONST.CONSTRAINT_CATALOG = T.CONSTRAINT_CATALOG and REF_CONST.CONSTRAINT_NAME = T.CONSTRAINT_NAME and REF_CONST.CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK on REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY' left 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' left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON FK.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME --for xml raw(''),elements
14 People are following this question.
