CODEWITHSUNDEEP
sqlsql-serverxml
Pistol Pete
Pistol Pete

Reputation: 193

Can you create a table from XML

This more a theoretical question as I have no use for it at this time. But can you CREATE TABLE from XML. For example if I run 

select    TABLE_CATALOG AS '@number', 
* from INFORMATION_SCHEMA.COLUMNS
order by 1
For XML Path ('root')

I get the XML readout like this for each data type in the table

<root number="testdb">
  <TABLE_CATALOG>testdb</TABLE_CATALOG>
  <TABLE_SCHEMA>dbo</TABLE_SCHEMA>
  <TABLE_NAME>tb_Population</TABLE_NAME>
  <COLUMN_NAME>ID</COLUMN_NAME>
  <ORDINAL_POSITION>1</ORDINAL_POSITION>
  <IS_NULLABLE>YES</IS_NULLABLE>
  <DATA_TYPE>varchar</DATA_TYPE>
  <CHARACTER_MAXIMUM_LENGTH>6</CHARACTER_MAXIMUM_LENGTH>
  <CHARACTER_OCTET_LENGTH>6</CHARACTER_OCTET_LENGTH>
  <CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
  <COLLATION_NAME>SQL_Latin1_General_CP1_CS_AS</COLLATION_NAME>
</root>

Can I take this xml and do something like this 

    CREATE TABLE [dbo].[xmlTest] 
From declare @XML xml = 
    '<root number="testdb">
      <TABLE_CATALOG>testdb</TABLE_CATALOG>
      <TABLE_SCHEMA>dbo</TABLE_SCHEMA>
      <TABLE_NAME>tb_Population</TABLE_NAME>
      <COLUMN_NAME>ID</COLUMN_NAME>
      <ORDINAL_POSITION>1</ORDINAL_POSITION>
      <IS_NULLABLE>YES</IS_NULLABLE>
      <DATA_TYPE>varchar</DATA_TYPE>
      <CHARACTER_MAXIMUM_LENGTH>6</CHARACTER_MAXIMUM_LENGTH>
      <CHARACTER_OCTET_LENGTH>6</CHARACTER_OCTET_LENGTH>
      <CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
      <COLLATION_NAME>SQL_Latin1_General_CP1_CS_AS</COLLATION_NAME>
    </root>'

Obviously this isn't correct at all but you get the idea. I just want to know if you can literally run a create table statement against xml. I could see this being used perhaps if you want recreate the table structure from one db into another but they are disparate systems or something like that. Or maybe I don't know what the hell I'm talking about too :-)

Upvotes: 1

Views: 56

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

This question is for me reinventing the wheel because you could simply generate table creation script using built-in scripter. But just for learning reason you can parse your XML and build Dynamic-SQL.

DECLARE @x XML =
N'<root number="testdb">
  <TABLE_CATALOG>testdb</TABLE_CATALOG>
  <TABLE_SCHEMA>dbo</TABLE_SCHEMA>
  <TABLE_NAME>tb_Population</TABLE_NAME>

  <COLUMN_NAME>ID</COLUMN_NAME>
  <ORDINAL_POSITION>1</ORDINAL_POSITION>
  <IS_NULLABLE>YES</IS_NULLABLE>
  <DATA_TYPE>varchar</DATA_TYPE>
  <CHARACTER_MAXIMUM_LENGTH>6</CHARACTER_MAXIMUM_LENGTH>
  <CHARACTER_OCTET_LENGTH>6</CHARACTER_OCTET_LENGTH>
  <CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
  <COLLATION_NAME>SQL_Latin1_General_CP1_CS_AS</COLLATION_NAME>
</root>';

DECLARE @database SYSNAME,
        @schema SYSNAME,
        @table SYSNAME,
        @column_name SYSNAME,
        @column_position VARCHAR(100),
        @is_nullable VARCHAR(10),
        @data_type VARCHAR(100),
        @character_maximum VARCHAR(100),
        @collation_name VARCHAR(100);


SELECT 
@database = t.c.value('TABLE_CATALOG[1]', 'SYSNAME'),
@schema = t.c.value('TABLE_SCHEMA[1]', 'SYSNAME'),
@table = t.c.value('TABLE_NAME[1]', 'SYSNAME'),
@column_name = t.c.value('COLUMN_NAME[1]', 'VARCHAR(100)'),
@column_position = t.c.value('TABLE_NAME[1]', 'VARCHAR(100)'),
 @is_nullable = t.c.value('IS_NULLABLE[1]', 'VARCHAR(100)'),
@data_type = t.c.value('DATA_TYPE[1]', 'VARCHAR(100)'),
@character_maximum = t.c.value('CHARACTER_MAXIMUM_LENGTH[1]', 'VARCHAR(100)'),
@collation_name = t.c.value('COLLATION_NAME[1]', 'VARCHAR(100)')
FROM @x.nodes('/root') AS t(c);

DECLARE @sql NVARCHAR(MAX) = 
N' CREATE TABLE @database.@schema.@table(
      @column_name @data_type@character_maximum @is_nullable @collation_name
);';

SET @sql = REPLACE(@sql, '@database', QUOTENAME(@database));
SET @sql = REPLACE(@sql, '@schema', QUOTENAME(@schema));
SET @sql = REPLACE(@sql, '@table', QUOTENAME(@table));
SET @sql = REPLACE(@sql, '@column_name', QUOTENAME(@column_name));
SET @sql = REPLACE(@sql, '@data_type', QUOTENAME(@data_type));
SET @sql = REPLACE(@sql, '@character_maximum', 
                           CASE WHEN @character_maximum IS NULL THEN ''
                           ELSE CONCAT('(', @character_maximum, ')')
                           END);
SET @sql = REPLACE(@sql, '@is_nullable',
                           CASE WHEN @is_nullable = 'YES' THEN 'NULL'
                             ELSE 'NOT NULL'
                           END);

SET @sql = REPLACE(@sql, '@collation_name', 
                          CASE WHEN @collation_name IS NULL THEN ''
                           ELSE CONCAT('COLLATE ', @collation_name)
                           END);
PRINT @sql;

--EXEC [dbo].[sp_executesql]
--      @sql;

LiveDemo

Output:

CREATE TABLE [testdb].[dbo].[tb_Population](
      [ID] [varchar](6) NULL COLLATE SQL_Latin1_General_CP1_CS_AS
);

Warning:

This is not production code, you should not rely on it. Only for demo purposes.

When you go this way you need:

  • handle corner cases
  • check many things your XML does not provide (PRIMARY/FOREIGN KEY, constraints, default values)
  • you are exposed to SQL Injection attack if you don't check each parameter
  • you need to loop through all columns, for demo I assumed you have only one
  • conditionaly change code depending of type
  • change XML structure to handle multiple columns
  • more and more

This task is possible but doing it with SQL is wasting time especially when you can simple click GENERATE SCRIPT.

Upvotes: 2

Related Questions