xml导入到sql表

以下是如何正确执行此操作。您需要在 XPath 表达式中指定正确的元素名称及其子属性。此外，最好使用精确的数据类型而不是通用的 VARCHAR(100)。

XML

<?xml version="1.0" encoding="UTF-8"?>
<SetupSheetData>
    <ReportHeading>
        <Heading>Setup Sheet</Heading>
    </ReportHeading>
    <Data>
        <rep_Doc Unit="Inches" a3AxisPart="0.001" Spline="0.001" FacetRegen="0.000"
                 ConfigurationComment="____________________"
                 ConfigurationName="____________________" ConfigurationNameCW="Mill - Inch- 0 [  ]"/>
        <rep_MchParams MchName="Mill - Inch" MchDesc="Sample Milling Machine" MchDuty="Medium duty"
                       MchType="This function has not been implemented for models with multiple sheetmetal base features (some of which may be hidden)."
                       MchController="M3Axis-Tutorial.CTL" MchNoOfAxis="Axis3"
                       MchModeFor4_5x="Simultaneous" MaxFeedRate="650.000"
                       MaxSpindleSpeed="12000.000" MchToolPos="20" MchSpindleTaper="CT 40"
                       MchHorsePower="30.000" MchTableTravelX="50.000" MchTableTravelY="20.000"
                       MchTableTravelZ="25.000" MchToolCribId="5" MchDefMch="1"
                       MchAvgToolChangeTime="0.200" MchRapidFeedRate="1000.000"
                       MchAvgIndexTime="0.050" MchMode4_5xDesc="Simultaneous" MchNoAxisDesc="None"
                       MchTypeDesc="Mill" MchToolCribPriority="1" MchUseToolCribToolsOnly="0"
                       MchRetractDistance="10.000" MchDisplayCutCompFirstMove="0"
                       MchOriginOffsetMethod="UseOpsetupTR" MchTotalMachiningTimeOfMachine="0.748"/>
        <rep_MchPosting DefineCoolantFrom="POST" DefineDiaLenOffsetFrom="POST"
                        PostDirPath="C:\Users\BConners\Desktop\Machining Testing for suppressed features\">
            <Param Name="Program number" Value="1"/>
            <Param Name="Part Thickness" Value="25.40000mm"/>
            <Param Name="5axis Arc Deviation" Value="0.02540mm"/>
        </rep_MchPosting>
        <rep_WorkPiece WorkPieceOriginX="0.000" WorkPieceOriginY="0.000" WorkPieceOriginZ="0.000"
                       WorkPieceLength="2.065" WorkPieceWidth="0.800" WorkPieceHeight="118.234"
                       WorkPieceOffset="0.000" WorkPieceTypeName="____________________"
                       WorkPieceMaterial="6061-T6" WorkPieceStockGroupName="Aluminum Alloys"
                       WorkPieceUnitsConvertFactor="-2"/>
        <rep_ConfigInfo>
            <Param Name="LinTol0Plc" Value="____________________"/>
            <Param Name="LinTol1Plc" Value="____________________"/>
            <Param Name="LinTol2Plc" Value="____________________"/>
            <Param Name="Revision" Value="05" EvaluatedValue="05"/>
            <Param Name="Author" Value="debarrid" EvaluatedValue="debarrid"/>
            <Param Name="Description2" Value="____________________"/>
            <Param Name="MaterialThickness" Value="____________________"/>
            <Param Name="RevisionApprovedByHistory" Value="____________________"/>
            <Param Name="RevisionApprovedDate" Value="____________________"/>
            <Param Name="RevisionApprovedDateHistory" Value="____________________"/>
            <Param Name="RevisionByHistory" Value="____________________"/>
            <Param Name="RevisionNoteHistory" Value="____________________"/>
            <Param Name="RevisionNumberHistory" Value="____________________"/>
            <Param Name="Type" Value="____________________"/>
            <Param Name="Apply Material to All Configurations" Value="Yes" EvaluatedValue="Yes"/>
            <Param Name="Project" Value="A-PARTS" EvaluatedValue="A-PARTS"/>
            <Param Name="Description" Value="FULL HEIGHT DOOR STOP EXTRUSION PROFILE"
                   EvaluatedValue="FULL HEIGHT DOOR STOP EXTRUSION PROFILE"/>
            <Param Name="Finish" Value="SEE DWG. L05-0522" EvaluatedValue="SEE DWG. L05-0522"/>
            <Param Name="MakeFrom" Value="RAW MATERIAL" EvaluatedValue="RAW MATERIAL"/>
            <Param Name="DrawnBy" Value="Genadij M" EvaluatedValue="Genadij M"/>
            <Param Name="DrawnDate" Value="May 09, 07" EvaluatedValue="May 09, 07"/>
            <Param Name="ApprovedBy" Value="Genadij M" EvaluatedValue="Genadij M"/>
            <Param Name="ApprovedDate" Value="May 09, 07" EvaluatedValue="May 09, 07"/>
            <Param Name="LinTol3Plc" Value="±0.015" EvaluatedValue="±0.015"/>
            <Param Name="Angular" Value="±1" EvaluatedValue="±1"/>
            <Param Name="UserDefined1" Value="____________________"/>
            <Param Name="UserDefined2" Value="STANDARD" EvaluatedValue="STANDARD"/>
            <Param Name="Number" Value="10-023" EvaluatedValue="10-023"/>
            <Param Name="RevisionBy" Value="PS" EvaluatedValue="PS"/>
            <Param Name="RevisionDate" Value="Dec 19, 07" EvaluatedValue="Dec 19, 07"/>
            <Param Name="RevisionApprovedBy" Value="ZB" EvaluatedValue="ZB"/>
            <Param Name="RevisionNote" Value='CHANGED FINISH WAS "CLEAR ANNOZIDED" ONLY;'
                   EvaluatedValue='CHANGED FINISH WAS "CLEAR ANNOZIDED" ONLY;'/>
            <Param Name="Material" Value='"[email protected]"'
                   EvaluatedValue="ALUMINUM 6063-T5"/>
            <Param Name="DesignType" Value="Standard" EvaluatedValue="Standard"/>
            <Param Name="PartNumberStock" Value="A21-0605-X" EvaluatedValue="A21-0605-X"/>
            <Param Name="SOLIDWORKS CAM Post processor" Value="M3Axis-Tutorial.CTL"
                   EvaluatedValue="M3Axis-Tutorial.CTL"/>
            <Param Name="SOLIDWORKS CAM Machine" Value="Mill - Inch" EvaluatedValue="Mill - Inch"/>
            <Param Name="SOLIDWORKS CAM Gcode file path"
                   Value="C:\Users\BConners\Desktop\Machining Testing for suppressed features\A23-3249L-X-BEN.txt"
                   EvaluatedValue="C:\Users\BConners\Desktop\Machining Testing for suppressed features\A23-3249L-X-BEN.txt"/>
            <Param Name="SOLIDWORKS CAM Created date" Value="01/13/2020::10:37"
                   EvaluatedValue="01/13/2020::10:37"/>
            <Param Name="SOLIDWORKS CAM Post file size" Value="951 bytes" EvaluatedValue="951 bytes"/>
            <Param Name="SOLIDWORKS CAM Created by" Value="BConners" EvaluatedValue="BConners"/>
        </rep_ConfigInfo>
        <rep_CustomInfo CustomInfoMode="1" CustomInfoCompany="____________________"
                        CustomInfoPartFile="A23-3249L-X-SlotsAndHoles.SLDPRT" Date="2020-01-20"
                        Time="37:20 AM" CustomInfoDateTime="2020-01-20  9:37:20 AM"
                        PartPath="C:\Users\BConners\Desktop\A23-3249L-X-SlotsAndHoles.SLDPRT"
                        ImagePath="C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS CAM\lang\English\Setup_Sheet_Images\A23-3249L-X-SlotsAndHoles\"
                        CustomInfoCheckedBy="____________________"
                        CustinfoClient="____________________"
                        CustomInfoDateComplete="____________________"
                        CustomInfoDepartment="____________________"
                        CustomInfoDestination="____________________"
                        CustomInfoDisposition="____________________"
                        CustomInfoDevision="____________________"
                        CustomInfoDocumentNumber="____________________"
                        CustomInfoEditor="____________________"
                        CustomInfoForwardedTo="____________________"
                        CustomInfoGroup="____________________"
                        CustomInfoLanguage="____________________"
                        CustomInfoMailStop="____________________"
                        CustomInfoMatter="____________________"
                        CustomInfoOffice="____________________"
                        CustomInfoOwner="____________________" CustomInfoProject="A-PARTS"
                        CustomInfoPublisher="____________________"
                        CustomInfoPurpose="____________________"
                        CustomInfoReceivedfrom="____________________"
                        CustomInfoReceivedBy="____________________"
                        CustomInfoRecordDate="____________________"
                        CustomInfoReference="____________________"
                        CustomInfoSource="____________________"
                        CustomInfoStatus="____________________"
                        CustomInfoTelephoneNumber="____________________"
                        CustomInfoTypist="____________________"
                        CustomInfoCompanyName="____________________"
                        CustomInfoProgrammer="____________________"
                        CustomInfoPartNo="____________________" CustomInfoRevision="05"
                        CustomInfoManufacturingApproval="____________________"
                        CustomInfoMFGAppDate="____________________"
                        CustomInfoAuthor="____________________"
                        CustomInfoKeywords="____________________"
                        CustomInfoComments="____________________"
                        CustomInfoTitle="____________________"
                        CustomInfoSubject="____________________"
                        CustomInfoCreated="2006-05-09 5:03:30 PM"
                        CustomInfoLastSaved="2020-01-13 10:37:59 AM"
                        CustomInfoLastSavedBy="BConners"/>
        <MillSetupSheetAttr>
            <MillOperation>
                <rep_Operation OperationName="Countersink1" Comment="____________________"
                               Description="COUNTERSINK" NoOfOPerations="5" OpNumber="3"
                               OperationID="277"/>
                <rep_MillOperParams ZFeedRate="22.643" MillSpindleSpeed="4528.658"
                                    SpindleDirection="CW" CounterSinkType="Spot Drilling"
                                    FirstPeckAmt="0.200" SubPeckAmt="0.100" RapidPlaneDist="1.000"
                                    ClearPlaneDist="0.100" Dwell="1.000"
                                    RapidPlaneType="Top of Stock" RetractType="Rapid Plane"
                                    FirstCutFrom="Top of Feature" Offset="0.000"
                                    OptimizationMethod="Upper Left" LastClosest="0"
                                    XYFeedRate="N.A." MinToolProtusionLength="N.A."
                                    CuttingSpeed="598.473"/>
                <rep_Tool ToolStnNo="18" ToolStnNoSub="0" ToolStnID="____________________"
                          ToolName="T18 - 5/8 X 90 Countersink"
                          ToolComment="5/8 HSS 90DEG 4FL COUNTERSINK" ToolMaterial="HSS"
                          CombId="____________________" ToolUsage="1" LengthOffset="1.000"
                          DiameterOffset="1.000"/>
                <rep_MillToolParams SizeDesignation="5/8 X 90" CutDia="0.625" OverAllLen="2.250"
                                    CounterSinkAngle="90.000" ShankDia="0.375" EndDia="0.000"
                                    ShoulderLen="0.625" NoOfTeeth="4" Protrusion="1.500"
                                    HandofCut="RIGHT HAND" Material="HSS"
                                    Comment="5/8 HSS 90DEG 4FL COUNTERSINK" MillToolCoolant="Flood"
                                    ToolImagePath="ToolsImages\CounterSink.bmp"/>
                <rep_MillHolderLibrary HolderNumber="Default" HolderComment="____________________"
                                       HolderShape="Basic" HolderType="None" HolderSpec="None"
                                       TopDia="3.000" BottomDia="1.500" OverallLen="4.000"
                                       BottomLen="1.500" UserDefinedName="None" MillTurn="1"/>
                <rep_ToolImagePath ToolImagePath="ToolsImages\Setup_1_Operation_3.bmp"/>
                <rep_Posting>
                    <Param Name="Absolute Incremental" Value="Absolute"/>
                    <Param Name="Coolant" Value="Flood"/>
                </rep_Posting>
                <rep_Feature MachDepth="0.178" FeatureName="Countersink Hole Group1 [Drill] [Sub9]"/>
                <rep_Relation RelationMachDepth="0.178" RelationTlpLen="124.724"
                              RelationTime="0.228" MinX="0.732" MinY="-54.883" MinZ="-0.178"
                              MaxX="0.732" MaxY="57.117" MaxZ="1.000"
                              StartComment="____________________" EndComment="____________________"
                              OpName="Countersink1" ToolStn="18" ZDepth="0.178"
                              MachiningDepth="0.178"
                              RadialTipCoordinateDispalyPoint="0.000000 , 0.000000 , 0.000000"
                              RadialCenterCoordinateDispalyPoint="0.000000 , 0.000000 , 0.000000"
                              DiameterTipCoordinateDispalyPoint="0.000000 , 0.000000 , 0.000000"
                              DiameterCenterCoordinateDispalyPoint="0.732500 , 57.117000 , 0.100000"
                              GageOffsetX="0.000" GageOffsetY="0.000" GageOffsetZ="5.500"/>
            </MillOperation>
        </MillSetupSheetAttr>
    </Data>
</SetupSheetData>

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    CustomInfoPartFile VARCHAR(100),
    OpNumber varchar(50) NOT NULL,
    OperationName varchar(100) NOT NULL,
    FeatureName varchar(100) NOT NULL,
    RelationTlpLen varchar(100) NOT NULL,
    MillSpindleSpeed varchar(100) NOT NULL,
    ZFeedRate varchar(100) NOT NULL,
    ToolName varchar(100) NOT NULL,
    ToolStnNo varchar(100) NOT NULL,
    RelationTime varchar(100) NOT NULL
);
-- DDL and sample data population, end

;WITH XmlFile (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK 'e:\Temp\CamworksReport.xml', SINGLE_BLOB) AS x
)
INSERT INTO @tbl (CustomInfoPartFile, OpNumber, OperationName, FeatureName, RelationTlpLen, MillSpindleSpeed, ZFeedRate, ToolName, ToolStnNo, RelationTime)
SELECT c.value('(../../rep_CustomInfo/@CustomInfoPartFile)[1]','VARCHAR(100)') AS [CustomInfoPartFile]
    , c.value('(rep_Operation/@OpNumber)[1]','INT') AS [OpNumber]
    , c.value('(rep_Operation/@OperationName)[1]','VARCHAR(100)') AS [OperationName]
    , c.value('(rep_Feature/@FeatureName)[1]','VARCHAR(100)') AS [FeatureName]
    , c.value('(rep_Relation/@RelationTlpLen)[1]','DECIMAL(10,3)') AS [RelationTlpLen]
    , c.value('(rep_MillOperParams/@MillSpindleSpeed)[1]','DECIMAL(10,3)') AS [MillSpindleSpeed]
    , c.value('(rep_MillOperParams/@ZFeedRate)[1]','DECIMAL(10,3)') AS [ZFeedRate]
    , c.value('(rep_Tool/@ToolName)[1]','VARCHAR(100)') AS [ToolName]
    , c.value('(rep_Tool/@ToolStnNo)[1]','INT') AS [ToolStnNo]
    , c.value('(rep_Relation/@RelationTime)[1]','DECIMAL(10,3)') AS [RelationTime]
FROM XmlFile CROSS APPLY xmlData.nodes('(/SetupSheetData/Data/MillSetupSheetAttr/MillOperation)') AS t(c);

-- test
SELECT * FROM @tbl;

输出

+----------------------------------+----------+---------------+----------------------------------------+----------------+------------------+-----------+----------------------------+-----------+--------------+
|        CustomInfoPartFile        | OpNumber | OperationName |              FeatureName               | RelationTlpLen | MillSpindleSpeed | ZFeedRate |          ToolName          | ToolStnNo | RelationTime |
+----------------------------------+----------+---------------+----------------------------------------+----------------+------------------+-----------+----------------------------+-----------+--------------+
| A23-3249L-X-SlotsAndHoles.SLDPRT |        3 | Countersink1  | Countersink Hole Group1 [Drill] [Sub9] |        124.724 |         4528.658 |    22.643 | T18 - 5/8 X 90 Countersink |        18 |        0.228 |
+----------------------------------+----------+---------------+----------------------------------------+----------------+------------------+-----------+----------------------------+-----------+--------------+