How to convert database table structure to XML file in sql server?

Question

How will I convert table Schema to XML format? Format is given below.

<Tables>
  <Table>
    <Name>courses</Name>
    <Schema>dbo</Schema>
    <Columns>
      <Column>
        <Name>id</Name>
        <DataType>int</DataType>
     </Column>
     <Column>
       <Name>page_name</Name>
       <DataType>nvarchar</DataType>
       <Length>50</Length>
     </Column>
    </Columns>
  </Table>
  <Table>
    <Name>course_details</Name>
    <Schema>dbo</Schema>
    .....
    .....
  </Table>
</Tables>

I am able to generate the structure for columns and tables separately. But I want to consolidated both. How is it possible? My SQL scripts

For Tables:

SELECT
Distinct
TABLE_NAME as Name,
TABLE_SCHEMA as [Schema]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),
Root('Tables')

For Columns:

SELECT
Column_Name as Name,
DATA_TYPE as DataType,
CHARACTER_MAXIMUM_LENGTH as [Length]
FROM INFORMATION_SCHEMA.COLUMNS
For XML PATH ('Column'),
Root('Columns')

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-10-09 17:18:43Z

5

The same query, but looks better:

SELECT TABLE_NAME as '@Name',        
(
    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'

    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')

edited Oct 9, 2012 at 17:18

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

answered Oct 9, 2012 at 17:15

Boris

511 silver badge1 bronze badge

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

Comments

Pavel Urbančík · Accepted Answer · 2012-07-18 15:14:28Z

2

You need to use the TYPE parameter of FOR XML PATH ..
Something like this:

SELECT  TABLE_NAME      as name,
        TABLE_SCHEMA    as [schema],
        (
            SELECT Column_Name as Name,
                    DATA_TYPE as DataType,
                    CHARACTER_MAXIMUM_LENGTH as [Length]
            FROM INFORMATION_SCHEMA.COLUMNS
            For XML PATH ('Column'),root('columns'), type
        )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),Root('Tables')

answered Jul 18, 2012 at 15:14

Pavel Urbančík

1,4969 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to convert database table structure to XML file in sql server?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related