<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="mill operations.xsl"?>
<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"/>I have an external .xml file that I would like to import some of the values into an sql table. I am new to sql programming but have attempted to write some code. The .xml file is a little different from some sample .xml files I have seen and I cannot get any of the values to populate the sql table. It identifies that there are 3 instances of the mill operations that I need, but there are no values being fed back to the table (just 3 blank rows). It seems in the .xml the value for the element is a huge string or something along those lines. I will attach a sample of the xml and the code I have written. Your help is much appreciated.
</MillOperation>
<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>USE FFGD
GO
IF OBJECT_ID('FFGD.dbo.CAMWORKS') IS NOT NULL
DROP TABLE FFGD.dbo.CAMWORKS
GO
CREATE TABLE FFGD.dbo.CAMWORKS
(
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,
)
GO
INSERT INTO FFGD.dbo.CAMWORKS (OpNumber, OperationName, FeatureName, RelationTlpLen, MillSpindleSpeed, ZFeedRate, ToolName, ToolStnNo, RelationTime)
SELECT
MY_XML.MillOperation.query('OpNumber').value('.', 'VARCHAR(50)'),
MY_XML.MillOperation.query('OperationName').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('FeatureName').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('RelationTlpLen').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('MillSpindleSpeed').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('ZFeedRate').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('ToolName').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('ToolStnNo').value('.', 'VARCHAR(100)'),
MY_XML.MillOperation.query('RelationTime').value('.', 'VARCHAR(100)')
FROM (SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'C:\Users\BConn\Desktop\CamworksReport.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('SetupSheetData/Data/MillSetupSheetAttr/MillOperation') AS MY_XML (MillOperation);Here is how to do it correctly. You need to specify correct element name and its child attribute in the XPath expressions. Also, it is better to use exact data types instead of the generic 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;
Output
+----------------------------------+----------+---------------+----------------------------------------+----------------+------------------+-----------+----------------------------+-----------+--------------+
| 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 |
+----------------------------------+----------+---------------+----------------------------------------+----------------+------------------+-----------+----------------------------+-----------+--------------+