如何将 XML 文件及其列名导入 SQL 表?

With this code you would get three derived tables with generated IDs to define their relation:

DECLARE @x XML=
N'<Data>
  <!-- IntelliScan - USC  -->
  <BatchNumber>1</BatchNumber>
  <ComputerName>PC-XDS1</ComputerName>
  <StartTime>2/16/2016 7:44:38 AM</StartTime>
  <EndTime>2/16/2016 8:14:47 AM</EndTime>
  <IdleTime>672</IdleTime>
  <ImagesDroppedFront>0</ImagesDroppedFront>
  <ImagesDroppedBack>205</ImagesDroppedBack>
  <JobName>Landscape Production NO MONEY</JobName>
  <JobNumber>001</JobNumber>
  <Operator>abwhite</Operator>
  <OutputFolderName>6604773004001</OutputFolderName>
  <PagesOperatorReplaced>2</PagesOperatorReplaced>
  <PagesOperatorDeleted>1</PagesOperatorDeleted>
  <PagesOperatorInserted>0</PagesOperatorInserted>
  <ProcessingDate>2/16/2016</ProcessingDate>
  <ProfileName> Multis Landscape</ProfileName>
  <RecoveredTime />
  <Status />
  <SystemDate>2/16/2016</SystemDate>
  <TotalItems>318</TotalItems>
  <TotalLevel1>0</TotalLevel1>
  <TotalLevel2>64</TotalLevel2>
  <TotalLevel3>0</TotalLevel3>
  <TotalImages>506</TotalImages>
  <TotalFrontIJP>321</TotalFrontIJP>
  <TotalBackIJP>0</TotalBackIJP>
  <TotalPostIJP>0</TotalPostIJP>
  <TransportType>DocuScan</TransportType>
  <TotalAutoFeed>298</TotalAutoFeed>
  <TotalManualFeed>23</TotalManualFeed>
  <TotalFeedOnTime>0</TotalFeedOnTime>
  <TotalSpeed1>0</TotalSpeed1>
  <TotalSpeed2>0</TotalSpeed2>
  <TotalSpeed3>321</TotalSpeed3>
  <XPTNumber>73</XPTNumber>
  <IR_Marked>0</IR_Marked>
  <IR_Reviewed>39</IR_Reviewed>
  <Start_DLN>6604773004001</Start_DLN>
  <End_DLN>6604773004061</End_DLN>
  <DocTypes>
    <Doctype ID="400">
      <!-- [ DocType400 ]  - DOCSEP -->
      <Name>DOCSEP</Name>
      <Count>64</Count>
    </Doctype>
    <Doctype ID="001">
      <!-- [ DocType001 ]  - Page -->
      <Name>Page</Name>
      <Count>254</Count>
    </Doctype>
  </DocTypes>
  <Jams>
    <Jam>
      <Message>5.10 Document Late To MultiPocket STK1 PKT1 Entrance Sensor</Message>
      <Count>3</Count>
      <Time>206</Time>
    </Jam>
    <Jam>
      <Message>1.6 Document Late to PostScan IJP Entrance sensor.</Message>
      <Count>2</Count>
      <Time>13</Time>
    </Jam>
    <Jam>
      <Message>1.4 Document Late to Reader On Doc sensor.</Message>
      <Count>3</Count>
      <Time>18</Time>
    </Jam>
    <Jam>
      <Message>0.125 Double feed detected. Document held at the handfeed track</Message>
      <Count>1</Count>
      <Time>16</Time>
    </Jam>
    <Jam>
      <Message>1.5 Document Late to PreScan IJP Entrance sensor.</Message>
      <Count>1</Count>
      <Time>3</Time>
    </Jam>
    <Jam>
      <Message>3.2 Unexpected Item Detected at Dbl Feed exit sensor.</Message>
      <Count>1</Count>
      <Time>43</Time>
    </Jam>
  </Jams>
</Data>';

--查询开始:

WITH DataNode AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Data_ID
          ,Data.value('BatchNumber[1]','int') AS BatchNumber
          ,Data.value('ComputerName[1]','nvarchar(max)') AS ComputerName
          --add all columns of the first level below Data
          ,Data.query('DocTypes') AS Doctype
          ,Data.query('Jams') AS Jam
    FROM @x.nodes('Data') AS A(Data)
)
,Doctypes AS
(
    SELECT DataNode.Data_ID
          ,ROW_NUMBER() OVER(PARTITION BY Data_ID ORDER BY (SELECT NULL)) AS Doctype_ID
          ,dt.value('@ID','int') AS DoctypeID
          ,dt.value('Name[1]','nvarchar(max)') AS DoctypeName
          ,dt.value('Count[1]','int') AS DoctypeCount
    FROM DataNode
    CROSS APPLY Doctype.nodes('DocTypes/Doctype') AS A(dt)
)
,Jams AS
(
    SELECT DataNode.Data_ID
          ,ROW_NUMBER() OVER(PARTITION BY Data_ID ORDER BY (SELECT NULL)) AS Jam_ID
          ,dt.value('Message[1]','nvarchar(max)') AS JamMessage
          ,dt.value('Count[1]','int') AS JamCount
          ,dt.value('Time[1]','int') AS JamTime
    FROM DataNode
    CROSS APPLY Jam.nodes('Jams/Jam') AS A(dt)
)
SELECT dn.*
      --add all Data columns here
      ,dt.Doctype_ID
      ,dt.DoctypeCount
      ,dt.DoctypeID
      ,dt.DoctypeName
      ,jm.Jam_ID
      ,jm.JamCount
      ,jm.JamMessage
      ,jm.JamTime
INTO #tmpResult
FROM DataNode AS dn
INNER JOIN DocTypes AS dt ON dt.Data_ID=dn.Data_ID
INNER JOIN Jams AS jm ON dt.Data_ID=jm.Data_ID;

SELECT DISTINCT Data_ID,BatchNumber,ComputerName INTO #MyDataTable FROM #tmpResult;
SELECT DISTINCT Data_ID,Doctype_ID,DoctypeID,DoctypeName,DoctypeCount INTO #MyDoctypeTable FROM #tmpResult;
SELECT DISTINCT Data_ID,Jam_ID,JamCount,JamMessage,JamTime INTO #MyJamTable FROM #tmpResult;
GO

SELECT * FROM #MyDataTable;
SELECT * FROM #MyDoctypeTable;
SELECT * FROM #MyJamTable;

GO
DROP TABLE #MyDataTable;
DROP TABLE #MyDoctypeTable;
DROP TABLE #MyJamTable;
DROP TABLE #tmpResult;