I am trying to insert data using a XML in SQL Server. The XML I am using is
<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&l开发者_运维百科t;/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 shred this XML in a temp table. I tried using
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 am getting Null in Offencecode and vehicle class. And as I understand, this is justified as <offenceCode>
is a child node of <offenceEntity>
. And I am not exclusively reading <offenceEntity>
node. Please help.
Based on your XML, you could use this XQuery SELECT
to extract your items from the 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 @input
with the variable or column that holds your XML (I've used @input as a test bed in my tests).
The output looks like this:
InfringementNumber Issue Agency Infringement Type Offence Code Vehicle Class Obligation Number
1234567891 017 1A 7777 2 obligation1
And of course, you can also do an INSERT INTO ....
and use the output from this SELECT
as the values to insert.
Update: if your XML column contains multiple entries (of /InfringementEntity
inside the /ArrayOfInfringementEntity
), you need to use a SELECT
like this:
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)
精彩评论