Bulk paste from XML to SQL Server

Question

I am trying to insert data using XML in SQL Server. XML used by me

<ArrayOfInfringementEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <InfringementEntity>
        <infringementNumber>12345678911</infringementNumber>
        <issueAgency>017</issueAgency>
        <infringementType>1A</infringementType>
        <infringementStatus>0</infringementStatus>
        <batchRecordId>0</batchRecordId>
        <incidentDate xsi:nil="true" />
        <infringementSource>OTS</infringementSource>
        <TypeOfNotice>0</TypeOfNotice>
        <offenceEntity>
            <offenceCode>7777</offenceCode>
            <offenceDate>1999-05-31T00:00:00</offenceDate>
            <offenceTime>121212</offenceTime>
            <offenceLocation>ST56789</offenceLocation>
            <offenceOwnerType>0</offenceOwnerType>
            <offenceSuburb>SOUTH YARRA</offenceSuburb>
            <site>ST56789</site>
            <detectedSpeed>70</detectedSpeed>
            <allegedSpeed>60</allegedSpeed>
            <permittedSpeed>50</permittedSpeed>
            <timeInRedLight>40</timeInRedLight>
            <tollAmount>140</tollAmount>
            <enforcementAllowance>310</enforcementAllowance>
            <lookUpFee>510</lookUpFee>
            <invoiceFee>130</invoiceFee>
        </offenceEntity>
        <vehicleEntity>
            <vehicleClass>2</vehicleClass>
            <vehicleMake>BMW</vehicleMake>
            <vehicleModel>FOUR WHEELER</vehicleModel>
            <bodyType>HEAVY</bodyType>
            <primaryColour>GRN</primaryColour>
            <manufactureYear>2010</manufactureYear>
            <gvm>111</gvm>
            <gcm>210</gcm>
            <registrationNumber>CBD-1111</registrationNumber>
            <registrationState>VIC</registrationState>
        </vehicleEntity>
        <obligationNumber>obligation1</obligationNumber>
        <isDebtorDeceased>false</isDebtorDeceased>
    </InfringementEntity>
</ArrayOfInfringementEntity>

I want to destroy this XML in temp table. I tried to use

create table #InfTemp

(infringementNumber Varchar(10),issueAgency varchar(5),infringementType varchar(5), offenceCode int,vehicleClass int,obligationNumber varchar(11)
)

Insert into #InfTemp
   SELECT  PLIxml.infringementNumber, PLIxml.issueAgency,PLIxml.infringementType,  
    PLIxml.offenceCode , PLIxml.vehicleClass ,PLIxml.obligationNumber 
  FROM  OPENXML (@output, 'ArrayOfInfringementEntity/InfringementEntity',2)   
  WITH
  (  infringementNumber Varchar(10),issueAgency varchar(5),infringementType varchar(5),offenceCode int,vehicleClass int,obligationNumber varchar(11)
   ) PLIxml 

But I get Null in Offencecode and car class. And, as I understand it, this is justified, since it <offenceCode>is a child of node of <offenceEntity>. And I do not exclusively read <offenceEntity>node. Please, help.

Answer 1

Based on your XML, you can use this XQuery SELECTto extract your elements from XML:

select
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/infringementNumber)[1]', 'VARCHAR(10)') 'InfringementNumber',
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/issueAgency)[1]', 'VARCHAR(5)') 'Issue Agency',
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/infringementType)[1]', 'VARCHAR(5)') 'Infringement Type',
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/offenceEntity/offenceCode)[1]', 'INT') 'Offence Code',
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/vehicleEntity/vehicleClass)[1]', 'INT') 'Vehicle Class',
    @input.value('(/ArrayOfInfringementEntity/InfringementEntity/obligationNumber)[1]', 'VARCHAR(11)') 'Obligation Number'

Replace with a @inputvariable or column that contains your XML (I used @input as a test layer in my tests).

:

InfringementNumber  Issue Agency    Infringement Type   Offence Code    Vehicle Class   Obligation Number
1234567891              017                1A              7777            2                obligation1

, , INSERT INTO .... SELECT .

:, XML (/InfringementEntity /ArrayOfInfringementEntity), SELECT :

SELECT
    InfrEntity.value('(infringementNumber)[1]', 'VARCHAR(10)') 'InfringementNumber',
    InfrEntity.value('(issueAgency)[1]', 'VARCHAR(5)') 'Issue Agency',
    InfrEntity.value('(infringementType)[1]', 'VARCHAR(5)') 'Infringement Type',
    InfrEntity.value('(offenceEntity/offenceCode)[1]', 'INT') 'Offence Code',
    InfrEntity.value('(vehicleEntity/vehicleClass)[1]', 'INT') 'Vehicle Class',
    InfrEntity.value('(obligationNumber)[1]', 'VARCHAR(11)') 'Obligation Number'
from
    (yourXMLcolumn).nodes('/ArrayOfInfringementEntity/InfringementEntity') as ArrInfr(InfrEntity)

Answer 2

SqlBulkCopy XML MS Sql-Server. : blogs.microsoft/importing-large-xml-files-to-sql-server-using-sqlbulkcopy