Parsing a manifest.xml of a table schema to script? I have a manifest.xml sent to me by a vendor that accompanies some extracts they are also sending.  I assume there should be a way to parse this into an actual table structure (or a script to create a table).  It's an extremely wide table (153 columns) and I'd rather not manually create a table if I can avoid it, especially since there are a variety of extracts coming and most are extremely wide.

Here's a sample of what the manifest.xml looks like that accompanies each file extract:

&lt;DocumentElement&gt;
  &lt;SchemaTable&gt;
    &lt;ColumnName&gt;somecolumn1&lt;/ColumnName&gt;
    &lt;ColumnOrdinal&gt;0&lt;/ColumnOrdinal&gt;
    &lt;ColumnSize&gt;4&lt;/ColumnSize&gt;
    &lt;NumericPrecision&gt;10&lt;/NumericPrecision&gt;
    &lt;NumericScale&gt;255&lt;/NumericScale&gt;
    &lt;IsUnique&gt;false&lt;/IsUnique&gt;
    &lt;BaseColumnName&gt;somecolumn1&lt;/BaseColumnName&gt;
    &lt;DataType&gt;System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/DataType&gt;
    &lt;AllowDBNull&gt;false&lt;/AllowDBNull&gt;
    &lt;ProviderType&gt;8&lt;/ProviderType&gt;
    &lt;IsIdentity&gt;false&lt;/IsIdentity&gt;
    &lt;IsAutoIncrement&gt;false&lt;/IsAutoIncrement&gt;
    &lt;IsRowVersion&gt;false&lt;/IsRowVersion&gt;
    &lt;IsLong&gt;false&lt;/IsLong&gt;
    &lt;ProviderSpecificDataType&gt;System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/ProviderSpecificDataType&gt;
    &lt;DataTypeName&gt;int&lt;/DataTypeName&gt;
    &lt;NonVersionedProviderType&gt;8&lt;/NonVersionedProviderType&gt;
  &lt;/SchemaTable&gt;
  &lt;SchemaTable&gt;
    &lt;ColumnName&gt;somecolumn2&lt;/ColumnName&gt;
    &lt;ColumnOrdinal&gt;1&lt;/ColumnOrdinal&gt;
    &lt;ColumnSize&gt;4&lt;/ColumnSize&gt;
    &lt;NumericPrecision&gt;10&lt;/NumericPrecision&gt;
    &lt;NumericScale&gt;255&lt;/NumericScale&gt;
    &lt;IsUnique&gt;false&lt;/IsUnique&gt;
    &lt;BaseColumnName&gt;somecolumn2&lt;/BaseColumnName&gt;
    &lt;DataType&gt;System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/DataType&gt;
    &lt;AllowDBNull&gt;false&lt;/AllowDBNull&gt;
    &lt;ProviderType&gt;8&lt;/ProviderType&gt;
    &lt;IsIdentity&gt;false&lt;/IsIdentity&gt;
    &lt;IsAutoIncrement&gt;false&lt;/IsAutoIncrement&gt;
    &lt;IsRowVersion&gt;false&lt;/IsRowVersion&gt;
    &lt;IsLong&gt;false&lt;/IsLong&gt;
    &lt;ProviderSpecificDataType&gt;System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/ProviderSpecificDataType&gt;
    &lt;DataTypeName&gt;int&lt;/DataTypeName&gt;
    &lt;NonVersionedProviderType&gt;8&lt;/NonVersionedProviderType&gt;
  &lt;/SchemaTable&gt;
  ...
&lt;/DocumentElement&gt;
Here's some code to get you started:

declare @doc xml = '
&lt;DocumentElement&gt;
  &lt;SchemaTable&gt;
    &lt;ColumnName&gt;somecolumn1&lt;/ColumnName&gt;
    &lt;ColumnOrdinal&gt;0&lt;/ColumnOrdinal&gt;
    &lt;ColumnSize&gt;4&lt;/ColumnSize&gt;
    &lt;NumericPrecision&gt;10&lt;/NumericPrecision&gt;
    &lt;NumericScale&gt;255&lt;/NumericScale&gt;
    &lt;IsUnique&gt;false&lt;/IsUnique&gt;
    &lt;BaseColumnName&gt;somecolumn1&lt;/BaseColumnName&gt;
    &lt;DataType&gt;System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/DataType&gt;
    &lt;AllowDBNull&gt;false&lt;/AllowDBNull&gt;
    &lt;ProviderType&gt;8&lt;/ProviderType&gt;
    &lt;IsIdentity&gt;false&lt;/IsIdentity&gt;
    &lt;IsAutoIncrement&gt;false&lt;/IsAutoIncrement&gt;
    &lt;IsRowVersion&gt;false&lt;/IsRowVersion&gt;
    &lt;IsLong&gt;false&lt;/IsLong&gt;
    &lt;ProviderSpecificDataType&gt;System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/ProviderSpecificDataType&gt;
    &lt;DataTypeName&gt;int&lt;/DataTypeName&gt;
    &lt;NonVersionedProviderType&gt;8&lt;/NonVersionedProviderType&gt;
  &lt;/SchemaTable&gt;
  &lt;SchemaTable&gt;
    &lt;ColumnName&gt;somecolumn2&lt;/ColumnName&gt;
    &lt;ColumnOrdinal&gt;1&lt;/ColumnOrdinal&gt;
    &lt;ColumnSize&gt;4&lt;/ColumnSize&gt;
    &lt;NumericPrecision&gt;10&lt;/NumericPrecision&gt;
    &lt;NumericScale&gt;255&lt;/NumericScale&gt;
    &lt;IsUnique&gt;false&lt;/IsUnique&gt;
    &lt;BaseColumnName&gt;somecolumn2&lt;/BaseColumnName&gt;
    &lt;DataType&gt;System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/DataType&gt;
    &lt;AllowDBNull&gt;false&lt;/AllowDBNull&gt;
    &lt;ProviderType&gt;8&lt;/ProviderType&gt;
    &lt;IsIdentity&gt;false&lt;/IsIdentity&gt;
    &lt;IsAutoIncrement&gt;false&lt;/IsAutoIncrement&gt;
    &lt;IsRowVersion&gt;false&lt;/IsRowVersion&gt;
    &lt;IsLong&gt;false&lt;/IsLong&gt;
    &lt;ProviderSpecificDataType&gt;System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/ProviderSpecificDataType&gt;
    &lt;DataTypeName&gt;int&lt;/DataTypeName&gt;
    &lt;NonVersionedProviderType&gt;8&lt;/NonVersionedProviderType&gt;
  &lt;/SchemaTable&gt;

&lt;/DocumentElement&gt;';

with q as
(
    select t.c.value('ColumnName[1]','nvarchar(200)') ColumnName,
           t.c.value('ColumnOrdinal[1]','int') ColumnOrdinal,
           t.c.value('DataTypeName[1]','varchar(50)') DataTypeName
    from @doc.nodes('/DocumentElement/SchemaTable') t(c)
)
select concat(case when ColumnOrdinal = 0 then '    ' else '   ,' end, quotename(columnname), ' ', DataTypeName, ' ') columnDeclaration
from q
order by ColumnOrdinal