How to extract database schema to xml content?

Question

I need to extract the database schema to xml content.

Is there any open source tool or library which can perform schema extraction operation?

Is there any procedure available to generate the DB schema to xml content?

Example:

create table sample(id numeric(5) primary key)

<Tables>
  <Table Name="sample" Type="Table">
    <Column Name="id" DataType="numeric" IsNullable="NO" IsIdentity="0" Constraint="PRIMARY KEY" />
  </Table>
</Tables>

With below query mentioned in the answer same thing I need to implement for index, procedures....

Or Is there any procedure available to generate the DB schema to xml content? @Erwin Smout — Sathish
– Sathish, Commented Jan 28, 2019 at 10:07
The point being : the criticism that applies to that particular [part of the] question is that it is "too broad". You haven't even specified whether your database is managed by SQL or something else. You also have not specified the XML structure you'd want (no .xsd, that is). — Erwin Smout
– Erwin Smout, Commented Jan 28, 2019 at 10:53

Vishal · Accepted Answer · 2019-01-28 10:13:58Z

1

SELECT TABLE_NAME AS '@Name', CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'Table' ELSE 'View' END AS '@Type',
(
    SELECT Column_Name as '@Name',
            DATA_TYPE as '@DataType',
            case data_type 
                when 'nvarchar' 
                then CHARACTER_MAXIMUM_LENGTH 
                when 'varchar'  
                then CHARACTER_MAXIMUM_LENGTH
                else null 
            end  as '@Length',
            IS_NULLABLE AS '@IsNullable',
            COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS '@IsIdentity',

(SELECT tc.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND cu.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.Column_Name) AS '@Constraint'
FROM INFORMATION_SCHEMA.COLUMNS 
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML PATH ('Table'),Root('Tables')

answered Jan 28, 2019 at 10:13

Vishal

3201 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sathish Over a year ago

My doubt regarding generation of xml content based on the DB schema. Will it display the schema as xml content? And I'm getting error

Vishal Over a year ago

Can you please specify what error you are getting? And yes this will generate XML content of the DB schema

Sathish Over a year ago

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. This error I am getting

Sathish Over a year ago

If a DB contains table alone it generates as xml

Sathish Over a year ago

How the same way we will have with index and procedure also right?

Collectives™ on Stack Overflow

How to extract database schema to xml content?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related