Home > database >  Uploading XML data into SQL Server
Uploading XML data into SQL Server

Time：10-14

This is a new task for me and I've spent the past week reading a lot about OPENXML, xpaths and all kinds of fun stuff but i am still struggling with pushing this XML data into my database. I cannot share the XML data as it is sensitive, but it is all elements for the most part with a couple siblings and a few child nodes. Here is my code:

SELECT DISTINCT
   MY_XML.Inspection.query('inspectionId').value('.', 'INT'),
   MY_XML.Inspection.query('InspReportID').value('.', 'VARCHAR(50)'),
   MY_XML.Inspection.query('InspectionPostDate').value('.', 'DATE'),
   MY_XML.Inspection.query('InspStartDate').value('.', 'DATE'),
   MY_XML.Inspection.query('InspStartTime').value('.', 'INT'),
   MY_XML.Inspection.query('InspEndTime').value('.', 'INT'),
   MY_XML.Inspection.query('InspectionLevelId').value('.', 'INT'),
   MY_XML.Inspection.query('InspectionLevelDesc').value('.', 'VARCHAR(50)'),
   MY_XML.Inspection.query('PostAccidentIndicator').value('.', 'VARCHAR(20)'),
   Location.L.query('InspLocationCode').value('.', 'VARCHAR(50)'),
   Location.L.query('InspLocationText').value('.', 'VARCHAR(50)'),
   Violations.V.query('InspTotalOOSVioNum').value('.', 'INT'),
   Driver.D.query('DriverLastName').value('.','VARCHAR(100)')

FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\InspectionXML2.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      OUTER APPLY MY_XML.nodes('Inspections/Inspection/InspMain') AS MY_XML (Inspection)
      OUTER APPLY MY_XML.nodes('Inspections/Inspection/InspMain/InspLocation') AS Location (L)
      OUTER APPLY MY_XML.nodes('Inspections/Inspection/InspMain/InspTotalCounts') AS Violations (V)
      OUTER APPLY MY_XML.nodes('Inspections/Inspection/Drivers/Driver') AS Driver (D)
ORDER BY 1;

This code works great for one inspection being uploaded but i need to upload several and the data gets mixed and matched all wrong! Please help, i am open to new methods. I am close to looking into an automatic converter so please help!

1)

<?xml version="1.0"?>
-<Inspections schemaVersionCode="2.3" sourceSystemCode="COMPASS">
-<Inspection>
-<InspMain>
<inspectionId>123</inspectionId>
<InspReportID>OH-123</InspReportID>
<InspectionPostDate>10/07/2021</InspectionPostDate>
<InspStartDate>10/07/2021</InspStartDate>
<InspStartTime>0945</InspStartTime>
<InspEndTime>1200</InspEndTime>
<InspectionLevelId>1</InspectionLevelId>
<InspectionLevelDesc>FULL</InspectionLevelDesc>
<PostAccidentIndicator>NO</PostAccidentIndicator>
-<InspLocation>
<InspLocationCode>FIXED SITE</InspLocationCode>
<InspLocationText>CAMBRIDGES</InspLocationText>
</InspLocation>
<InspHazmatTypeCode/>
-<InspTotalCounts>
<InspTotalOOSVioNum>2</InspTotalOOSVioNum>
</InspTotalCounts>
</InspMain>
-<Drivers>
-<Driver>
<DriverLastName>RODNEY</DriverLastName>
<DriverBirthDate>12/31/1900</DriverBirthDate>
<DriverLicenseID>123</DriverLicenseID>
<DriverLicenseStateCode>OH</DriverLicenseStateCode>
</Driver>
</Drivers>
-<Vehicles>
-<Vehicle>
<VehicleUnitNum>1</VehicleUnitNum>
<VehicleUnitTypeCode>TRUCK TRACTOR</VehicleUnitTypeCode>
<VehicleMakeCode>INTL</VehicleMakeCode>
<VehicleCompanyID>123</VehicleCompanyID>
<VehicleLicenseID>AG123</VehicleLicenseID>
<VehicleLicenseStateCode>PA</VehicleLicenseStateCode>
<IEPDotNumber/>
</Vehicle>
-<Vehicle>
<VehicleUnitNum>2</VehicleUnitNum>
<VehicleUnitTypeCode>SEMI-TRAILER</VehicleUnitTypeCode>
<VehicleMakeCode>TRLK</VehicleMakeCode>
<VehicleCompanyID>1234</VehicleCompanyID>
<VehicleLicenseID>1234</VehicleLicenseID>
<VehicleLicenseStateCode>OK</VehicleLicenseStateCode>
<IEPDotNumber/>
</Vehicle>
</Vehicles>
-<Violations>
-<Violation>
<VioSeqID>5</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>YES</VioOOSFlag>
<VehicleUnitCode>D</VehicleUnitCode>
<ViolPartSection>395.8(a)(1)</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>NO LOG BOOK,LOG NOT CURRENT,GENERAL LOG VIOLATIONS</ViolationCategory>
<SectionDesc>ELD - No record of duty status (ELD Required)</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
-<Violation>
<VioSeqID>1</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>NO</VioOOSFlag>
<VehicleUnitCode>D</VehicleUnitCode>
<ViolPartSection>392.2</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>ALL OTHER DRIVER VIOLATIONS</ViolationCategory>
<SectionDesc>Miscellaneous Traffic Law Violation</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
-<Violation>
<VioSeqID>3</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>NO</VioOOSFlag>
<VehicleUnitCode>1</VehicleUnitCode>
<ViolPartSection>393.45(d)</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>BRAKES, ALL OTHERS</ViolationCategory>
<SectionDesc>Brake Connections with Leaks Under Vehicle</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
-<Violation>
<VioSeqID>2</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>NO</VioOOSFlag>
<VehicleUnitCode>1</VehicleUnitCode>
<ViolPartSection>393.45(d)</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>BRAKES, ALL OTHERS</ViolationCategory>
<SectionDesc>Brake Connections with Leaks - Connection to Power Unit</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
-<Violation>
<VioSeqID>4</VioSeqID>
<VioRegSectionCode/>
<VioDescText>NO</VioDescText>
<VioOOSFlag>YES</VioOOSFlag>
<VehicleUnitCode>1</VehicleUnitCode>
<ViolPartSection>396.3(a)(1)</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>BRAKES, ALL OTHERS</ViolationCategory>
<SectionDesc>Brake system pressure loss</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
-<Violation>
<VioSeqID>6</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>NO</VioOOSFlag>
<VehicleUnitCode>1</VehicleUnitCode>
<ViolPartSection>393.9(a)</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>LIGHTING</ViolationCategory>
<SectionDesc>Inoperative turn signal</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
</Violations>
<StatusCode>YES</StatusCode>
</Inspection>
-<Inspection>
-<InspMain>
<inspectionId>12345</inspectionId>
<InspReportID>NC-1234</InspReportID>
<InspectionPostDate>10/08/2021</InspectionPostDate>
<InspStartDate>10/08/2021</InspStartDate>
<InspStartTime>0800</InspStartTime>
<InspEndTime>0900</InspEndTime>
<InspectionLevelId>3</InspectionLevelId>
<InspectionLevelDesc>DRIVER-ONLY</InspectionLevelDesc>
<PostAccidentIndicator>NO</PostAccidentIndicator>
-<InspLocation>
<InspLocationCode>ROADSIDE</InspLocationCode>
<InspLocationText>GOLden</InspLocationText>
</InspLocation>
<InspHazmatTypeCode/>
-<InspTotalCounts>
<InspTotalOOSVioNum>0</InspTotalOOSVioNum>
</InspTotalCounts>
</InspMain>
-<Drivers>
-<Driver>
<DriverLastName>joe schmoe</DriverLastName>
<DriverBirthDate>05/05/1888</DriverBirthDate>
<DriverLicenseID>1234</DriverLicenseID>
<DriverLicenseStateCode>FL</DriverLicenseStateCode>
</Driver>
</Drivers>
-<Vehicles>
-<Vehicle>
<VehicleUnitNum>1</VehicleUnitNum>
<VehicleUnitTypeCode>TRUCK TRACTOR</VehicleUnitTypeCode>
<VehicleMakeCode>PTRB</VehicleMakeCode>
<VehicleCompanyID>1234</VehicleCompanyID>
<VehicleLicenseID>1234</VehicleLicenseID>
<VehicleLicenseStateCode>TX</VehicleLicenseStateCode>
<IEPDotNumber/>
</Vehicle>
-<Vehicle>
<VehicleUnitNum>2</VehicleUnitNum>
<VehicleUnitTypeCode>SEMI-TRAILER</VehicleUnitTypeCode>
<VehicleMakeCode>FONA</VehicleMakeCode>
<VehicleCompanyID>1234</VehicleCompanyID>
<VehicleLicenseID>1234</VehicleLicenseID>
<VehicleLicenseStateCode>TX</VehicleLicenseStateCode>
<IEPDotNumber/>
</Vehicle>
</Vehicles>
-<Violations>
-<Violation>
<VioSeqID>1</VioSeqID>
<VioRegSectionCode>NO</VioRegSectionCode>
<VioDescText>NO</VioDescText>
<VioOOSFlag>NO</VioOOSFlag>
<VehicleUnitCode>D</VehicleUnitCode>
<ViolPartSection>392.2</ViolPartSection>
<StateCitationNumber/>
<ViolationCategory>FAILURE TO OBEY TRAFFIC CONTROL DEVICE</ViolationCategory>
<SectionDesc>Failure to obey traffic control device</SectionDesc>
<IEPAttrFlag/>
<StateCitationResult>N/A</StateCitationResult>
</Violation>
</Violations>
<StatusCode>NO</StatusCode>
</Inspection>
</Inspections>

2)upload inspections into db 3)each inspection in one row, no mismatching 4) SQL Server 2016

CodePudding user response：

Please try the following solution.

SQL

WITH rs (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK N'e:\Temp\InspectionXML2.xml', SINGLE_BLOB) AS x
)
--INSERT INTO targetTable (...)
SELECT c.value('(InspMain/inspectionId/text())[1]', 'INT') AS inspectionId
    , c.value('(InspMain/InspReportID/text())[1]', 'VARCHAR(50)') AS InspReportID
    , c.value('(InspMain/InspectionPostDate/text())[1]', 'VARCHAR(20)') AS InspectionPostDate
    , c.value('(InspMain/InspStartDate/text())[1]', 'DATE') AS InspStartDate
    , c.value('(InspMain/InspStartTime/text())[1]', 'INT')AS InspStartTime
    , c.value('(InspMain/InspEndTime/text())[1]', 'INT') AS InspEndTime
    , c.value('(InspMain/InspectionLevelId/text())[1]', 'INT') AS InspectionLevelId
    , c.value('(InspMain/InspectionLevelDesc/text())[1]', 'VARCHAR(50)') AS InspectionLevelDesc
    , c.value('(InspMain/PostAccidentIndicator/text())[1]', 'VARCHAR(20)') AS PostAccidentIndicator
    , c.value('(InspMain/InspLocation/InspLocationCode/text())[1]', 'VARCHAR(50)') AS InspLocationCode
    , c.value('(InspMain/InspLocation/InspLocationText/text())[1]', 'VARCHAR(50)') AS InspLocationText
    , c.value('(InspMain/InspTotalCounts/InspTotalOOSVioNum/text())[1]', 'INT') AS InspTotalOOSVioNum
    , c.value('(Drivers/Driver/DriverLastName/text())[1]','VARCHAR(100)') AS DriverLastName
FROM rs 
   CROSS APPLY xmlData.nodes('/Inspections/Inspection') AS t(c);

Output

 -------------- -------------- -------------------- --------------- --------------- ------------- ------------------- --------------------- ----------------------- ------------------ ------------------ -------------------- ---------------- 
| inspectionId | InspReportID | InspectionPostDate | InspStartDate | InspStartTime | InspEndTime | InspectionLevelId | InspectionLevelDesc | PostAccidentIndicator | InspLocationCode | InspLocationText | InspTotalOOSVioNum | DriverLastName |
 -------------- -------------- -------------------- --------------- --------------- ------------- ------------------- --------------------- ----------------------- ------------------ ------------------ -------------------- ---------------- 
|          123 | OH123        | 10/07/2021         | 2021-10-07    |           945 |        1200 |                 1 | FULL                | NO                    | FIXED SITE       | CAMBRIDGES       |                  2 | RODNEY         |
|        12345 | NC1234       | 10/08/2021         | 2021-10-08    |           800 |         900 |                 3 | DRIVERONLY          | NO                    | ROADSIDE         | GOLden           |                  0 | joe schmoe     |
 -------------- -------------- -------------------- --------------- --------------- ------------- ------------------- --------------------- ----------------------- ------------------ ------------------ -------------------- ----------------

Page link：https//www.codepudding.com/database/151976.html

Prev:Postgres equivalent of ANY ... SATISFIES in n1ql to combine ANY and IN
Next:SQL - SUM the values from a COLUMN and put each value in a SEPARATE column
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding