1

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:

<DocumentElement>
  <SchemaTable>
    <ColumnName>somecolumn1</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn1</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  <SchemaTable>
    <ColumnName>somecolumn2</ColumnName>
    <ColumnOrdinal>1</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn2</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  ...
</DocumentElement>
  • Please tag your question with SQL Server version. – SqlWorldWide Jan 9 '18 at 17:06
  • Have you asked the vendor the export and import mechanisms they recommend? – Nic Jan 9 '18 at 19:03
0

Here's some code to get you started:

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

</DocumentElement>';

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.