sql-server - 粉碎 XML 文件未返回所有数据-6ren

sql-server - 粉碎 XML 文件未返回所有数据

转载作者：行者123 更新时间：2023-12-04 01:11:57

我有一个如下所示的 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>

我使用此查询分解 xml 文件并将其放入临时表中:

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)

当我在表上执行 SELECT 时，__MigrationHistory 和 CoatingCost 表数据存在，但 ContactLens 数据不存在

有人能看出 XML 文件或分解 xml 的查询有什么问题吗？

更新这是 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>
      <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>';

这是查询:

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)

最佳答案

当我改变一个CROSS APPLY你有一个OUTER APPLY丢失的表在那里。

您的表“ContactLens”没有子树 <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)

关于sql-server - 粉碎 XML 文件未返回所有数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39619912/

文章推荐： Scala 流性能

文章推荐： oracle - 是否有可能知道给定查询会触发哪些触发器？

文章推荐： streaming - 使用 VLC 将文件流式传输为 RTSP

Android - 加速下降(粉碎)
Video explaining for those who does not understand 此答案未正确回答，请尝试使用其他解决方案来回答(100 Bounty 已过期) 同样的问题，但解释
android - 线程在旋转时使 Activity 粉碎
我有一个使用线程来从文件中读取一些内容的 Activity 。问题是，如果用户决定在线程运行时旋转屏幕，应用程序就会崩溃。我知道我可以使用以下方法阻止屏幕旋转: android:screenOri
c++ - 粉碎 C++ VPTR
我正在上计算机安全课并且正在阅读 http://phrack.org/issues/56/8.html .在 bo3.cpp 中，作者创建了他自己的 VTABLE，并覆盖 VPTR 以指向他的 VTA
hadoop - 粉碎 HDFS 中的小文件
我们在 Mesos 0.17 上针对 CDH5 运行 Spark 0.9.1。到目前为止，我们继续使用 CDH 系列的“mr1”版本，以便我们可以运行 filecrush。项目在我们的小文件上。由于各
mysql - 粉碎 MySQL 行
我知道如何删除文件以使它们无法恢复。但是，如何以导致相同结果的方式从 POSIX 环境中的 MySQL 表中删除行？在继续删除该行之前，我目前正在使用与原始数据长度相同的无效字符串重写所有数据。它有效
sql-server - 粉碎 XML 文件未返回所有数据
我有一个如下所示的 XML 文件:
javascript - Three.js 粉碎/爆炸平面几何体
我正在尝试使用 Tween 和平面几何在 Three.js 中创建玻璃 splinter 效果，但有点迷失。网格/几何体不会随补间更新，如果我在第一次渲染之前调用 shatter()，您可以看到补间正
sql-server - 使用 T-SQL 粉碎 XML - 从组内部提取 Line 值的正确语法是什么？
我正在尝试使用我在 XML 中接收的一些日志数据。在清理数据以使其成为有效的 XML 之后，我在 SQL Server 中获得了 XML 数据包。 (并从 JSON 包装器等中获取其他属性) 但是现
ruby-on-rails - 开始时 slim-rails gem 粉碎 `register_config_processor' : wrong number of arguments
当我将 slim-rails 添加到 Gemfile 并启动我的应用程序时，它崩溃并出现以下错误: /Users/rado/.rbenv/versions/2.3.1/gemsets/project/

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql-server - 粉碎 XML 文件未返回所有数据