青青草国产,一区二区三区亚洲,青青草97国产精品麻豆

本文介紹了如何將 XML 文件及其列名導(dǎo)入 SQL 表?的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！

問(wèn)題描述

限時(shí)送ChatGPT賬號(hào)..

我有 12 個(gè) XML 文件，我必須在 SQL Server 的單個(gè)表中導(dǎo)入它們，其中一個(gè)在下面，請(qǐng)幫我將這些文件放入帶有列名的表格式中.

I have 12 XML files which I have to import in single Table in SQL Server, one of it is below, Please help me to put those files in a Table format with their column names.

<?xml version="1.0" encoding="UTF-16"?>
<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></RecoveredTime>
<Status></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>

我已經(jīng)在運(yùn)行一個(gè)查詢(xún)，但問(wèn)題是，在 Data/DoctTypes 中有多個(gè) ID，在 Data/Jams/Jam 中有多個(gè) Message、Count、time 列.我已經(jīng)在表名 XMLFiles 和列名 XMLData 中加載了 xml 文件，現(xiàn)在需要從 xml 文件中檢索 xml 文件中的列，并在單個(gè)表中使用它們的值.那么如何檢索這些多列或數(shù)據(jù)呢?我的查詢(xún)?nèi)缦拢?em class="showen">

I am already running one query but the problem is that, In Data/DoctTypes there are multiple ID and in Data/Jams/Jam there are multiple Message,Count,time columns. I have already loaded xml files in table name XMLFiles and column name XMLData and now need to retrieve the columns inside xml files from xml files with their values in a single table. So how can I retrieve those multiple Columns or data? My query is below,

Select  XMLData.value('(/Data/BatchNumber)[1]','int' ) BatchNumber,
    XMLData.value('(/Data/ComputerName)[1]','varchar(50)' ) ComputerName,
    XMLData.value('(/Data/StartTime)[1]','DateTime' ) StartTime,
    XMLData.value('(/Data/EndTime)[1]','DateTime' ) EndTime,
    XMLData.value('(/Data/IdleTime)[1]','int' ) IdleTime,
    XMLData.value('(/Data/ImagesDroppedFront)[1]','int' ) ImagesDroppedFront,
    XMLData.value('(/Data/ImagesDroppedBack)[1]','int' ) ImagesDroppedBack,
    XMLData.value('(/Data/JobName)[1]','varchar(100)' ) JobName,
    XMLData.value('(/Data/JobNumber)[1]','varchar(10)' ) JobNumber,
    XMLData.value('(/Data/Operator)[1]','varchar(50)' ) Operator,
    XMLData.value('(/Data/OutputFolderName)[1]','varchar(20)' ) OutputFolderName,
    XMLData.value('(/Data/PagesOperatorReplaced)[1]','int' ) PagesOperatorReplaced,
    XMLData.value('(/Data/PagesOperatorDeleted)[1]','int' ) PagesOperatorDeleted,
    XMLData.value('(/Data/PagesOperatorInserted)[1]','int' ) PagesOperatorInserted,
    XMLData.value('(/Data/ProcessingDate)[1]','Date' ) ProcessingDate,
    XMLData.value('(/Data/ProfileName)[1]','varchar(50)' ) ProfileName,
    XMLData.value('(/Data/RecoveredTime)[1]','Time' ) RecoveredTime,
    XMLData.value('(/Data/Status)[1]','varchar(50)' ) [Status],
    XMLData.value('(/Data/SystemDate)[1]','Date' ) SystemDate,
    XMLData.value('(/Data/TotalItems)[1]','int' ) TotalItems,
    XMLData.value('(/Data/TotalLevel1)[1]','int' ) TotalLevel1,
    XMLData.value('(/Data/TotalLevel2)[1]','int' ) TotalLevel2,
    XMLData.value('(/Data/TotalLevel3)[1]','int' ) TotalLevel3,
    XMLData.value('(/Data/TotalImages)[1]','int' ) TotalImages,
    XMLData.value('(/Data/TotalFrontIJP)[1]','int' ) TotalFrontIJP,
    XMLData.value('(/Data/TotalBackIJP)[1]','int' ) TotalBackIJP,
    XMLData.value('(/Data/TotalPostIJP)[1]','int' ) TotalPostIJP,
    XMLData.value('(/Data/TransportType)[1]','varchar(50)' ) TransportType,
    XMLData.value('(/Data/TotalAutoFeed)[1]','int' ) TotalAutoFeed,
    XMLData.value('(/Data/TotalManualFeed)[1]','int' ) TotalManualFeed,
    XMLData.value('(/Data/TotalFeedOnTime)[1]','int' ) TotalFeedOnTime,
    XMLData.value('(/Data/TotalSpeed1)[1]','int' ) TotalSpeed1,
    XMLData.value('(/Data/TotalSpeed2)[1]','int' ) TotalSpeed2,
    XMLData.value('(/Data/TotalSpeed3)[1]','int' ) TotalSpeed3,
    XMLData.value('(/Data/XPTNumber)[1]','int' ) XPTNumber,
    XMLData.value('(/Data/IR_Marked)[1]','int' ) IR_Marked,
    XMLData.value('(/Data/IR_Reviewed)[1]','int' ) IR_Reviewed,
    ISNULL(XMLData.value('(/Data/Start_DLN)[1]','varchar(20)'),'' ) Start_DLN,
    ISNULL(XMLData.value('(/Data/End_DLN)[1]','varchar(20)'),'' ) End_DLN,
    XMLData.value('(/Data/DocTypes/Doctype/@ID)[1]','int') ID,
    XMLData.value('(/Data/DocTypes/Doctype/Name)[1]','varchar(50)' ) Name,
    XMLData.value('(/Data/DocTypes/Doctype/Count)[1]','int' ) [Count],
    ISNULL(XMLData.value('(/Data/Jams/Jam/Message)[1]','varchar(1000)'),'') [Message],
    ISNULL(XMLData.value('(/Data/Jams/Jam/Count)[1]','int' ),'') [Count],
    ISNULL(XMLData.value('(/Data/Jams/Jam/Time)[1]','int' ),'') [Time]
    From XMLFiles X

推薦答案

您的 XML 具有 1:n 關(guān)系的嵌套數(shù)據(jù).要將其放入正確設(shè)計(jì)的數(shù)據(jù)結(jié)構(gòu)中，需要單獨(dú)的表.

Your XML has nested data with 1:n relation. To put this in properly designed data structures needs separate tables.

使用此代碼，您將獲得三個(gè)帶有生成 ID 的派生表來(lái)定義它們的關(guān)系:

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

--查詢(xún)開(kāi)始:

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;

這篇關(guān)于如何將 XML 文件及其列名導(dǎo)入 SQL 表?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

如何將 XML 文件及其列名導(dǎo)入 SQL 表?

問(wèn)題描述

推薦答案

相關(guān)文檔推薦