Xml Shredding , Sql Server

Xml Shredding

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

declare @xml xml

select @xml = '<extensionG>
  <Tables>
    <Table TName="__MigrationHistory" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="MigrationId" DataType="nvarchar" DataTypeMaxLength="300" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="ContextKey" DataType="nvarchar" DataTypeMaxLength="600" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="Model" DataType="varbinary" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="ProductVersion" DataType="nvarchar" DataTypeMaxLength="64" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
    </Table>
    <Table TName="CoatingCost" Schema="dbo">
      <Columns>
        <Column CName="CoatingCostId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="CoatingId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="LabId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Cost" DataType="money" DataTypeMaxLength="8" DataTypePrecision="19" DataTypeScale="4" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Price" DataType="money" DataTypeMaxLength="8" DataTypePrecision="19" DataTypeScale="4" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
    </Table>
    <Table TName="ContactLens" Schema="dbo">
      <Columns>
        <Column CName="ProductId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
    </Table>
  </Tables>
</extensionG>'

SELECT DBTables.Name.value('@TName', 'varchar(100)') AS TableName, DBTables.Name.value('@Schema', 'varchar(20)') AS SchemaName, DBTables.Name.value('@TextImageOnFileGroup', 'varchar(20)') AS TextImageFileGroup,
        Cols.C.value('@CName', 'varchar(100)') AS ColName, Cols.C.value('@DataType', 'varchar(20)') AS DataType, Cols.C.value('@DataTypeMaxLength', 'varchar(5)') AS DataTypeMaxLength,
        Cols.C.value('@DataTypePrecision', 'varchar(5)') AS DataTypePrecision,  Cols.C.value('@DataTypeScale', 'varchar(5)') AS DataTypeScale,  
        Cols.C.value('@IsIdentityColumn', 'varchar(1)') AS IsIdentityColumn, Cols.C.value('@IdentitySeedValue', 'varchar(5)') AS IdentitySeedValue, Cols.C.value('@IdentityIncrementValue', 'varchar(5)') AS IdentityIncrementValue,
        Cols.C.value('@IsColumnNullable', 'varchar(1)') AS IsColumnNullable, Cols.C.value('@PrimaryKey', 'varchar(1)') AS PrimaryKey    
    FROM @xml.nodes('//Tables/Table') AS DBTables(Name)     
            CROSS APPLY DBTables.Name.nodes('Columns/Column') AS Cols(C)


run \| edit \| history \| help	0