Shredding XML file is not returning all of the data

Question

I have an XML file that looks like this:

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

I shred the xml file using this query and put it into a temporary table:

INSERT INTO #TargetDBObjects
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 @XmlTargetDB.nodes('//Tables/Table') AS DBTables(Name)     
            CROSS APPLY DBTables.Name.nodes('Columns/Column') AS Cols(C)

When I do a SELECT on the table, the __MigrationHistory and CoatingCost table data is present but not the ContactLens data

Can anybody see anything wrong with the XML file or the query that shreds the xml?

UPDATE This is a more complete example of the xml file and the entire query that shreds the document:

    '<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>
      <Indexes>
        <Index IndexName="PK_dbo.__MigrationHistory" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="MigrationId" IsDescendingSort="0" OrdinalPosition="1" />
          <IndexColumn ICName="ContextKey" IsDescendingSort="0" OrdinalPosition="2" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="Activity" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="ActivityId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="ApplicationId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Name" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Description" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Code" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
      <ForeignKeys>
        <ForeignKey FK_Name="FK_dbo.Activity_dbo.Application_ApplicationId" ParentColumn="ApplicationId" ReferenceTable="Application" ReferenceColumn="ApplicationId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="1" />
      </ForeignKeys>
      <Indexes>
        <Index IndexName="PK_dbo.Activity" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ActivityId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
        <Index IndexName="IX_ApplicationId" PrimaryKeyIndex="0" IndexDescription="NONCLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ApplicationId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="AdHocContacts" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="AdHocContactId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="PatientProfileId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Name" DataType="varchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Fax" DataType="varchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Email" DataType="varchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
      </Columns>
    </Table>
    <Table TName="AdjustmentType" Schema="dbo">
      <Columns>
        <Column CName="AdjustmentId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="AdjustmentName" DataType="nvarchar" DataTypeMaxLength="100" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
      <Indexes>
        <Index IndexName="PK_AdjustmentType" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="AdjustmentId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="Application" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="ApplicationId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="Code" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Name" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
      <Indexes>
        <Index IndexName="PK_dbo.Application" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ApplicationId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="ApplicationCategory" Schema="dbo">
      <Columns>
        <Column CName="ApplicationId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="CategoryId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
      </Columns>
      <ForeignKeys>
        <ForeignKey FK_Name="FK_ApplicationCategory_Application" ParentColumn="ApplicationId" ReferenceTable="Application" ReferenceColumn="ApplicationId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="0" />
        <ForeignKey FK_Name="FK_ApplicationCategory_Category" ParentColumn="CategoryId" ReferenceTable="Category" ReferenceColumn="CategoryId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="0" />
      </ForeignKeys>
      <Indexes>
        <Index IndexName="PK_ApplicationCategory" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ApplicationId" IsDescendingSort="0" OrdinalPosition="1" />
          <IndexColumn ICName="CategoryId" IsDescendingSort="0" OrdinalPosition="2" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="ApplicationSetting" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="SettingId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="ApplicationId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="SettingKey" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Value" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
      </Columns>
    </Table>
    <Table TName="Bookmark" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="BookmarkId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="UserName" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Url" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Name" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
      </Columns>
    </Table>
    <Table TName="Bundle" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="BundleId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="Name" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
      </Columns>
      <Indexes>
        <Index IndexName="PK_dbo.Bundle" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="BundleId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="BundleProduct" Schema="dbo">
      <Columns>
        <Column CName="BundleId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="ProductId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
      </Columns>
      <ForeignKeys>
        <ForeignKey FK_Name="FK_dbo.BundleProduct_dbo.Bundle_BundleId" ParentColumn="BundleId" ReferenceTable="Bundle" ReferenceColumn="BundleId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="1" />
        <ForeignKey FK_Name="FK_dbo.BundleProduct_dbo.Product_ProductId" ParentColumn="ProductId" ReferenceTable="Product" ReferenceColumn="ProductId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="1" />
      </ForeignKeys>
      <Indexes>
        <Index IndexName="PK_dbo.BundleProduct" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="BundleId" IsDescendingSort="0" OrdinalPosition="1" />
          <IndexColumn ICName="ProductId" IsDescendingSort="0" OrdinalPosition="2" />
        </Index>
        <Index IndexName="IX_BundleId" PrimaryKeyIndex="0" IndexDescription="NONCLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="BundleId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
        <Index IndexName="IX_ProductId" PrimaryKeyIndex="0" IndexDescription="NONCLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ProductId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
      <Triggers>
        <Trigger TriggerName="TestTrigger2" TrigDefinition="-- =============================================&#xD;&#xA;-- Author:&#x9;&#x9;&lt;Author,,Name&gt;&#xD;&#xA;-- Create date: &lt;Create Date,,&gt;&#xD;&#xA;-- Description:&#x9;&lt;Description,,&gt;&#xD;&#xA;-- =============================================&#xD;&#xA;CREATE TRIGGER TestTrigger2&#xD;&#xA;   ON  BundleProduct &#xD;&#xA;   AFTER DELETE,UPDATE&#xD;&#xA;AS &#xD;&#xA;BEGIN&#xD;&#xA;&#x9;-- SET NOCOUNT ON added to prevent extra result sets from&#xD;&#xA;&#x9;-- interfering with SELECT statements.&#xD;&#xA;&#x9;SET NOCOUNT ON;&#xD;&#xA;&#xD;&#xA;    -- Insert statements for trigger here&#xD;&#xA;&#x9;select * from Edging;&#xD;&#xA;END&#xD;&#xA;" IsUpdateTrig="1" IsDeleteTrig="1" IsInsertTrig="0" IsAfterTrig="1" IsInsteadOfTrig="0" IsDisabled="0" />
      </Triggers>
    </Table>
    <Table TName="Category" Schema="dbo">
      <Columns>
        <Column CName="CategoryId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="Name" DataType="varchar" DataTypeMaxLength="50" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
      </Columns>
      <Indexes>
        <Index IndexName="PK_Category" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="CategoryId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
    <Table TName="Coating" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="CoatingId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="1" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="1" />
        <Column CName="Name" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="Inactive" DataType="bit" DataTypeMaxLength="1" DataTypePrecision="1" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="0" PrimaryKey="0" />
        <Column CName="ProcedureCode" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IdentitySeedValue="1" IdentityIncrementValue="1" IsColumnNullable="1" PrimaryKey="0" />
      </Columns>
      <Indexes>
        <Index IndexName="PK_Coatings" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="CoatingId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </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>
      <ForeignKeys>
        <ForeignKey FK_Name="FK_CoatingCost_Coating" ParentColumn="CoatingId" ReferenceTable="Coating" ReferenceColumn="CoatingId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="0" />
        <ForeignKey FK_Name="FK_CoatingCost_Lab" ParentColumn="LabId" ReferenceTable="Lab" ReferenceColumn="LabId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="0" />
      </ForeignKeys>
      <Indexes>
        <Index IndexName="PK_CoatingCost" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="CoatingCostId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </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>
    <Table TName="Contacts" Schema="dbo" TextImageOnFileGroup="PRIMARY">
      <Columns>
        <Column CName="BaseCurve" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Cylinder" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Diameter" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="Power" DataType="nvarchar" DataTypeMaxLength="-1" DataTypePrecision="0" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="1" PrimaryKey="0" />
        <Column CName="ProductId" DataType="int" DataTypeMaxLength="4" DataTypePrecision="10" DataTypeScale="0" IsIdentityColumn="0" IsColumnNullable="0" PrimaryKey="1" />
      </Columns>
      <ForeignKeys>
        <ForeignKey FK_Name="FK_Contacts_Product" ParentColumn="ProductId" ReferenceTable="Product" ReferenceColumn="ProductId" IsDisabled="0" HasUpdateRefAction="0" HasDeleteRefAction="0" />
      </ForeignKeys>
      <Indexes>
        <Index IndexName="PK_Contacts" PrimaryKeyIndex="1" IndexDescription="CLUSTERED" PadIndex="0" Statistics_NoRecompute="0" IgnoreDupKey="0" AllowRowLocks="1" AllowPageLocks="1">
          <IndexColumn ICName="ProductId" IsDescendingSort="0" OrdinalPosition="1" />
        </Index>
      </Indexes>
    </Table>
  </Tables>
</extensionG>';

This is the query:

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,

        [Indexes].I.value('@IndexName', 'varchar(100)') AS IndexName,  [Indexes].I.value('@PrimaryKeyIndex', 'varchar(1)') AS PrimaryKeyIndex,  [Indexes].I.value('@IsUnique', 'varchar(1)') AS IsUnique, 
            [Indexes].I.value('@IndexDescription', 'varchar(120)') AS IndexDescription,
            [Indexes].I.value('@PadIndex', 'varchar(1)') AS PadIndex, [Indexes].I.value('@Statistics_NoRecompute', 'varchar(1)') AS StatisticsNoRecompute, [Indexes].I.value('@IgnoreDupKey', 'varchar(1)') AS IgnoreDupKey,
            [Indexes].I.value('@AllowRowLocks', 'varchar(1)') AS AllowRowLocks, [Indexes].I.value('@AllowPageLocks', 'varchar(1)') AS AllowPageLocks,
        [IndexColumn].IC.value('@ICName', 'varchar(100)') AS IndexColumnName, [IndexColumn].IC.value('@IsDescendingSort', 'varchar(1)') AS IsDescendingSort, 
        [IndexColumn].IC.value('@OrdinalPosition', 'varchar(2)') AS OrdinalPosition,

        ForeignKey.FK.value('@FK_Name', 'varchar(100)') AS ForeignKeyName,  
        ForeignKey.FK.value('@ParentColumn', 'varchar(50)') AS ParentColumn,   
        ForeignKey.FK.value('@ReferenceTable', 'varchar(100)') AS ReferenceTable, ForeignKey.FK.value('@ReferenceColumn', 'varchar(50)') AS ReferenceColumn,  
        ForeignKey.FK.value('@IsDisabled', 'varchar(1)') AS IsDisabled, ForeignKey.FK.value('@HasUpdateRefAction', 'varchar(1)') AS HasUpdateRefAction,  ForeignKey.FK.value('@HasDeleteRefAction', 'varchar(1)') AS HasDeleteRefAction,

        Defaults.D.value('@DefName', 'varchar(100)') AS DefaultName,  Defaults.D.value('@DefValue', 'varchar(100)') AS DefaultValue, Defaults.D.value('@DefColumnName', 'varchar(100)') AS DefColumnName,

        Checks.C.value('@CkName', 'varchar(100)') AS CheckName, Checks.C.value('@CkDefinition', 'varchar(500)') AS CkDefinition, Checks.C.value('@IsCkDisabled', 'varchar(1)') AS IsCkDisabled,

        [Triggers].T.value('@TriggerName', 'varchar(100)') AS TriggerName, [Triggers].T.value('@TrigDefinition', 'varchar(max)') AS TrigDefinition,
            [Triggers].T.value('@IsUpdateTrig', 'varchar(1)') AS IsUpdateTrigger, [Triggers].T.value('@IsDeleteTrig', 'varchar(1)') AS IsDeleteTrigger, [Triggers].T.value('@IsInsertTrig', 'varchar(1)') AS IsInsertTrigger,
            [Triggers].T.value('@IsAfterTrig', 'varchar(1)') AS IsAfterTrigger, [Triggers].T.value('@IsInsteadOfTrig', 'varchar(1)') AS IsInsteadOfTrigger, [Triggers].T.value('@IsDisabled', 'varchar(1)') AS IsDisabledTrigger

    FROM @XmlTargetDB.nodes('//Tables/Table') AS DBTables(Name)     
            CROSS APPLY DBTables.Name.nodes('Columns/Column') AS Cols(C)
            OUTER APPLY DBTables.Name.nodes('Indexes/Index') AS [Indexes](I)
            CROSS APPLY I.nodes('IndexColumn') AS [IndexColumn](IC)
            OUTER APPLY DBTables.Name.nodes('ForeignKeys/ForeignKey') AS ForeignKey(FK)
            OUTER APPLY DBTables.Name.nodes('DefaultConstraints/Default') AS Defaults(D)
            OUTER APPLY DBTables.Name.nodes('CheckConstraints/Check') AS Checks(C)
            OUTER APPLY DBTables.Name.nodes('Triggers/Trigger') AS [Triggers](T)

What are you missing? When I run your `select` I see 5 rows for "_MigrationHistory", 5 rows for "CoatingCost" and 1 row for "ContactLens". — Shnugo, Sep 21 '16 at 15:04
Deleted my answer, seems to work fine: http://rextester.com/LFSA38950 — Matt Evans, Sep 21 '16 at 15:08
Are you explicitly creating #TargetDBObjects. If so can you show that SQL, as well as your SELECT statement — Matt Evans, Sep 21 '16 at 15:10
I tried to only post the part of the query and xml file that was a problem. But I limited it too much. I will update the post to include an xml example and the whole ugly query. — Gloria Santin, Sep 21 '16 at 15:20

Shnugo · Accepted Answer · 2016-09-21T15:30:05.480

When I change the one CROSS APPLY you have there to an OUTER APPLY the missing table is there.

Your table "ContactLens" does not have a sub-tree <indexes> ...

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,

    [Indexes].I.value('@IndexName', 'varchar(100)') AS IndexName,  [Indexes].I.value('@PrimaryKeyIndex', 'varchar(1)') AS PrimaryKeyIndex,  [Indexes].I.value('@IsUnique', 'varchar(1)') AS IsUnique, 
        [Indexes].I.value('@IndexDescription', 'varchar(120)') AS IndexDescription,
        [Indexes].I.value('@PadIndex', 'varchar(1)') AS PadIndex, [Indexes].I.value('@Statistics_NoRecompute', 'varchar(1)') AS StatisticsNoRecompute, [Indexes].I.value('@IgnoreDupKey', 'varchar(1)') AS IgnoreDupKey,
        [Indexes].I.value('@AllowRowLocks', 'varchar(1)') AS AllowRowLocks, [Indexes].I.value('@AllowPageLocks', 'varchar(1)') AS AllowPageLocks,
    [IndexColumn].IC.value('@ICName', 'varchar(100)') AS IndexColumnName, [IndexColumn].IC.value('@IsDescendingSort', 'varchar(1)') AS IsDescendingSort, 
    [IndexColumn].IC.value('@OrdinalPosition', 'varchar(2)') AS OrdinalPosition,

    ForeignKey.FK.value('@FK_Name', 'varchar(100)') AS ForeignKeyName,  
    ForeignKey.FK.value('@ParentColumn', 'varchar(50)') AS ParentColumn,   
    ForeignKey.FK.value('@ReferenceTable', 'varchar(100)') AS ReferenceTable, ForeignKey.FK.value('@ReferenceColumn', 'varchar(50)') AS ReferenceColumn,  
    ForeignKey.FK.value('@IsDisabled', 'varchar(1)') AS IsDisabled, ForeignKey.FK.value('@HasUpdateRefAction', 'varchar(1)') AS HasUpdateRefAction,  ForeignKey.FK.value('@HasDeleteRefAction', 'varchar(1)') AS HasDeleteRefAction,

    Defaults.D.value('@DefName', 'varchar(100)') AS DefaultName,  Defaults.D.value('@DefValue', 'varchar(100)') AS DefaultValue, Defaults.D.value('@DefColumnName', 'varchar(100)') AS DefColumnName,

    Checks.C.value('@CkName', 'varchar(100)') AS CheckName, Checks.C.value('@CkDefinition', 'varchar(500)') AS CkDefinition, Checks.C.value('@IsCkDisabled', 'varchar(1)') AS IsCkDisabled,

    [Triggers].T.value('@TriggerName', 'varchar(100)') AS TriggerName, [Triggers].T.value('@TrigDefinition', 'varchar(max)') AS TrigDefinition,
        [Triggers].T.value('@IsUpdateTrig', 'varchar(1)') AS IsUpdateTrigger, [Triggers].T.value('@IsDeleteTrig', 'varchar(1)') AS IsDeleteTrigger, [Triggers].T.value('@IsInsertTrig', 'varchar(1)') AS IsInsertTrigger,
        [Triggers].T.value('@IsAfterTrig', 'varchar(1)') AS IsAfterTrigger, [Triggers].T.value('@IsInsteadOfTrig', 'varchar(1)') AS IsInsteadOfTrigger, [Triggers].T.value('@IsDisabled', 'varchar(1)') AS IsDisabledTrigger

FROM @XmlTargetDB.nodes('//Tables/Table') AS DBTables(Name)     
        CROSS APPLY DBTables.Name.nodes('Columns/Column') AS Cols(C)
        OUTER APPLY DBTables.Name.nodes('Indexes/Index') AS [Indexes](I)
        OUTER APPLY I.nodes('IndexColumn') AS [IndexColumn](IC)
        OUTER APPLY DBTables.Name.nodes('ForeignKeys/ForeignKey') AS ForeignKey(FK)
        OUTER APPLY DBTables.Name.nodes('DefaultConstraints/Default') AS Defaults(D)
        OUTER APPLY DBTables.Name.nodes('CheckConstraints/Check') AS Checks(C)
        OUTER APPLY DBTables.Name.nodes('Triggers/Trigger') AS [Triggers](T)

I was not giving you all of the xml or query because I *thought* it was not necessary. So when I limit the xml and query to what I posted it does return all rows. So, it has to be with the other part of the xml or query. The entire xml and query are very large. — Gloria Santin, Sep 21 '16 at 15:13
I posted a more complete example and the entire query that shreds the xml. It has the problem that the table `ContactLens` does not appear in the results. — Gloria Santin, Sep 21 '16 at 15:24
Thanks again! changing the `CROSS APPLY` to `OUTER` did the trick! — Gloria Santin, Sep 21 '16 at 15:32

Shredding XML file is not returning all of the data

1 Answers1