Hi,

I'm keep getting the errors when querying a handfull of attributes in my .xml file which is in attachment. Would you please help? I appreciate any inputs.

Here is a list of attributes that I need to query out the values:

<AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
<AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA, 95118</AddressFullText>
<InjuredWas_driver>1</InjuredWas_driver>
<PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
<MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
<CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
<Time-AllPages>1547</Time-AllPages>

Here is my codes:
CREATE DATABASE OPENXMLTesting
GO

USE OPENXMLTesting
GO


CREATE TABLE xmltest
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

DECLARE @xmlfile xml
SELECT @xmlfile = x
FROM OPENROWSET(BULK 'C:\TestCase.xml', SINGLE_BLOB) AS xmlfile(x)

----SELECT @xmlfile;

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlfile
SELECT *
FROM OPENXML(@hDoc, '/Agency', 1)
WITH
(
AccidentReportNumber-AllPages varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
InjuredWas_driver int '/involvedPeople',
PedestrianActionA-AllPages int,
MotorVehicleInvolvedWithC-AllPages int,
CollisionOccurredDate-AllPages date,
Time-AllPages int
)


Here is the error:
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '-'.

Completion time: 2020-06-10T09:23:46.0548202-07:00


XML file content:
<agency xmlns="" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/
envelope" xmlns:fjs1="http://www.versaterm.com/rms/ns/RMS_7.2" xmlns:fjs2="http://www.versaterm.com/rms/types/RMS_7.2" formLock="off" project_name="mva_california" form_version="065" form_path="site/version/" formname="vtm_california_mva_v1">
<PageNum />
<Pages />
<TrafficUnit>
<TrafficUnit_Number>1</TrafficUnit_Number>
<Driver>
<BlankOption />
<TrafficUnit_Number />
<ID_Num>442240070</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<AddressFullText>1554 GLENCREST CT,SAN JOSE, CA, 95118</AddressFullText>
<AddressStreetText>1554 GLENCREST CT</AddressStreetText>
<AddressCityStateZip>SAN JOSE, CA, 95118</AddressCityStateZip>
<City>SAN JOSE</City>
<State>CA</State>
<Zip>95118</Zip>
<StateOfIssue>CA</StateOfIssue>
<LicenseClass>01</LicenseClass>
<Sex>F</Sex>
<Hair>BRO</Hair>
<Eyes>BRO</Eyes>
<Height>5'06</Height>
<Weight>140</Weight>
<Race>W</Race>
<DateOfBirth>09-20-1985</DateOfBirth>
<BirthDateYear>1985</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>20</BirthDateDay>
<Age>34</Age>
<ChargeDescriptionText />
</Driver>
<Vehicle>
<BlankOption />
<ID_Num>211222</ID_Num>
<RoleTrans>Involved (ACCIDENTS&amp;STR CHKS)</RoleTrans>
<RoleNumber>1</RoleNumber>
<ModelYear>2020</ModelYear>
<Make>TOYT</Make>
<Model>SNA</Model>
<Style>4H</Style>
<Colour>WHI/</Colour>
<StateOfIssue>CA</StateOfIssue>
<AddressFullText />
<CityState />
<Zip />
<VehicleType>1</VehicleType>
<NumberOfOccupants />
<InsuranceName>AAA</InsuranceName>
<InsuranceExpiryDate />
</Vehicle>
<MovementProcedingCollisionB />
<MovementProcedingCollisionC />
<MovementProcedingCollisionA />
<MovementProcedingCollisionE>1</MovementProcedingCollisionE>
<VehicleOwner>
<BlankOption />
<ID_Num>442240070</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<Seat_position />
<AddressFullText>1554 GLENCREST CT</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>34</Age>
<Sex>F</Sex>
<BirthDateYear>1985</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>20</BirthDateDay>
</VehicleOwner>
<NameSameAsDriver />
<AddrSameAsDriver />
<TrafficUnitDriverOtherCheck />
<TrafficUnitDriverBikeCheck />
<TrafficUnitDriverParkedCheck />
<TrafficUnitPedestrianCheck />
<TrafficUnitDriverCheck>1</TrafficUnitDriverCheck>
<DispositionVehicleByOfficer />
<DispositionVehicleByDriver>1</DispositionVehicleByDriver>
<DispositionVehicleByOther />
<PriorMechanicalDefectsNone />
<PriorMechanicalDefectsNarr />
<DescribeVehDamageUnk />
<DescribeVehDamageNone />
<DescribeVehDamageMinor />
<DescribeVehDamageMod />
<DescribeVehDamageMajor>1</DescribeVehDamageMajor>
<DescribeVehDamageRollOver />
<Damage_14 />
<Damage_13 />
<Damage_11>1</Damage_11>
<Damage_9>1</Damage_9>
<Damage_7 />
<Damage_4 />
<Damage_3 />
<Damage_2 />
<Damage_12 />
<Damage_1 />
<Damage_10>1</Damage_10>
<Damage_8 />
<Damage_6 />
<Damage_5 />
<VehicleType1 />
<VehicleType2 />
<SpecialInformationA />
<SpecialInformationB />
<SpecialInformationC />
<SpecialInformationE />
<SpecialInformationD />
<SpecialInformationF />
<SpecialInformationG />
<SpecialInformationI />
<SpecialInformationH />
<SpecialInformationJ />
<SpecialInformationK />
<SpecialInformationL />
<SpecialInformationM />
<SpecialInformationN />
<SpecialInformationO />
<OtherAssociatedFactorsC />
<OtherAssociatedFactorsB />
<OtherAssociatedFactorsA />
<OtherAssociatedFactorsE />
<OtherAssociatedFactorsF />
<OtherAssociatedFactorsG />
<OtherAssociatedFactorsH />
<OtherAssociatedFactorsI />
<OtherAssociatedFactorsJ />
<OtherAssociatedFactorsK />
<OtherAssociatedFactorsL />
<OtherAssociatedFactorsM />
<OtherAssociatedFactorsN />
<OtherAssociatedFactorsO />
<MovementProcedingCollisionD />
<MovementProcedingCollisionF />
<MovementProcedingCollisionG />
<MovementProcedingCollisionH />
<MovementProcedingCollisionI />
<MovementProcedingCollisionJ />
<MovementProcedingCollisionK />
<MovementProcedingCollisionL />
<MovementProcedingCollisionM />
<MovementProcedingCollisionN />
<MovementProcedingCollisionO />
<MovementProcedingCollisionP />
<MovementProcedingCollisionQ />
<MovementProcedingCollisionR />
<SobrietyDrugPhysicalA>1</SobrietyDrugPhysicalA>
<SobrietyDrugPhysicalC />
<SobrietyDrugPhysicalB />
<SobrietyDrugPhysicalE />
<SobrietyDrugPhysicalD />
<SobrietyDrugPhysicalF />
<SobrietyDrugPhysicalG />
<SobrietyDrugPhysicalH />
<SobrietyDrugPhysicalI />
<OnDutyEMV />
<DriverAirBag-Box-16>M</DriverAirBag-Box-16>
<DriverSafetyEquipmentUsed-Box-18>G</DriverSafetyEquipmentUsed-Box-18>
<Direction-Box-5>SE</Direction-Box-5>
<OnStreetOrHighway>BRANHAM LN</OnStreetOrHighway>
<SpeedLimit>35</SpeedLimit>
<DispositionText />
<OtherAssociatedFactorsAText />
<OtherAssociatedFactorsACitedYesNO />
<CAL_T />
<MC_MX />
<CA />
<DOT />
<TCP_PSC />
</TrafficUnit>
<TrafficUnit>
<TrafficUnit_Number>2</TrafficUnit_Number>
<Driver>
<BlankOption />
<TrafficUnit_Number />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA, 95118</AddressFullText>
<AddressStreetText>1493 CRESTHAVEN LN</AddressStreetText>
<AddressCityStateZip>SAN JOSE, CA, 95118</AddressCityStateZip>
<City>SAN JOSE</City>
<State>CA</State>
<Zip>95118</Zip>
<StateOfIssue>CA</StateOfIssue>
<LicenseClass>01</LicenseClass>
<Sex>M</Sex>
<Hair>BLK</Hair>
<Eyes>BRO</Eyes>
<Height>5'6</Height>
<Weight>138</Weight>
<Race>A</Race>
<DateOfBirth>09-29-1984</DateOfBirth>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
<Age>35</Age>
<ChargeDescriptionText />
</Driver>
<Vehicle>
<BlankOption />
<ID_Num>211223</ID_Num>
<RoleTrans>Involved (ACCIDENTS&amp;STR CHKS)</RoleTrans>
<RoleNumber>2</RoleNumber>
<ModelYear>2015</ModelYear>
<Make>VOLK</Make>
<Model>TGN</Model>
<Style>4H</Style>
<Colour>GRY/</Colour>
<StateOfIssue>CA</StateOfIssue>
<AddressFullText />
<CityState />
<Zip />
<VehicleType>1</VehicleType>
<NumberOfOccupants />
<InsuranceName>GEICO</InsuranceName>
<InsuranceExpiryDate />
</Vehicle>
<MovementProcedingCollisionB>1</MovementProcedingCollisionB>
<MovementProcedingCollisionC />
<MovementProcedingCollisionA />
<MovementProcedingCollisionE />
<VehicleOwner>
<BlankOption />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>35</Age>
<Sex>M</Sex>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
</VehicleOwner>
<NameSameAsDriver />
<AddrSameAsDriver />
<TrafficUnitDriverOtherCheck />
<TrafficUnitDriverBikeCheck />
<TrafficUnitDriverParkedCheck />
<TrafficUnitPedestrianCheck />
<TrafficUnitDriverCheck>1</TrafficUnitDriverCheck>
<DispositionVehicleByOfficer />
<DispositionVehicleByDriver>1</DispositionVehicleByDriver>
<DispositionVehicleByOther />
<PriorMechanicalDefectsNone>1</PriorMechanicalDefectsNone>
<PriorMechanicalDefectsNarr />
<DescribeVehDamageUnk />
<DescribeVehDamageNone />
<DescribeVehDamageMinor />
<DescribeVehDamageMod />
<DescribeVehDamageMajor>1</DescribeVehDamageMajor>
<DescribeVehDamageRollOver />
<Damage_14 />
<Damage_13 />
<Damage_11 />
<Damage_9>1</Damage_9>
<Damage_7 />
<Damage_4 />
<Damage_3 />
<Damage_2 />
<Damage_12 />
<Damage_1 />
<Damage_10>1</Damage_10>
<Damage_8>1</Damage_8>
<Damage_6 />
<Damage_5 />
<VehicleType1 />
<VehicleType2 />
<SpecialInformationA />
<SpecialInformationB />
<SpecialInformationC />
<SpecialInformationE />
<SpecialInformationD />
<SpecialInformationF />
<SpecialInformationG />
<SpecialInformationI />
<SpecialInformationH />
<SpecialInformationJ />
<SpecialInformationK />
<SpecialInformationL />
<SpecialInformationM />
<SpecialInformationN />
<SpecialInformationO />
<OtherAssociatedFactorsC />
<OtherAssociatedFactorsB />
<OtherAssociatedFactorsA />
<OtherAssociatedFactorsE />
<OtherAssociatedFactorsF />
<OtherAssociatedFactorsG />
<OtherAssociatedFactorsH />
<OtherAssociatedFactorsI />
<OtherAssociatedFactorsJ />
<OtherAssociatedFactorsK />
<OtherAssociatedFactorsL />
<OtherAssociatedFactorsM />
<OtherAssociatedFactorsN />
<OtherAssociatedFactorsO />
<MovementProcedingCollisionD />
<MovementProcedingCollisionF />
<MovementProcedingCollisionG />
<MovementProcedingCollisionH />
<MovementProcedingCollisionI />
<MovementProcedingCollisionJ />
<MovementProcedingCollisionK />
<MovementProcedingCollisionL />
<MovementProcedingCollisionM />
<MovementProcedingCollisionN />
<MovementProcedingCollisionO />
<MovementProcedingCollisionP />
<MovementProcedingCollisionQ />
<MovementProcedingCollisionR />
<SobrietyDrugPhysicalA>1</SobrietyDrugPhysicalA>
<SobrietyDrugPhysicalC />
<SobrietyDrugPhysicalB />
<SobrietyDrugPhysicalE />
<SobrietyDrugPhysicalD />
<SobrietyDrugPhysicalF />
<SobrietyDrugPhysicalG />
<SobrietyDrugPhysicalH />
<SobrietyDrugPhysicalI />
<OnDutyEMV />
<DriverAirBag-Box-16>L</DriverAirBag-Box-16>
<DriverSafetyEquipmentUsed-Box-18>G</DriverSafetyEquipmentUsed-Box-18>
<Direction-Box-5>W</Direction-Box-5>
<OnStreetOrHighway>BRANHAM LN</OnStreetOrHighway>
<SpeedLimit>35</SpeedLimit>
<DispositionText />
<OtherAssociatedFactorsAText />
<OtherAssociatedFactorsACitedYesNO />
<CAL_T />
<MC_MX />
<CA />
<DOT />
<TCP_PSC />
</TrafficUnit>
<ReportingDistrict-AllPages>SOUTHERN</ReportingDistrict-AllPages>
<InvolvedPeople>
<InvolvedPeople_Number>1</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>35</Age>
<Sex>M</Sex>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury>1</ExtentOfInjury_OtherVisInjury>
<ExtentOfInjury_ComplaintOfPain>1</ExtentOfInjury_ComplaintOfPain>
<InjuredWas_driver>1</InjuredWas_driver>
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>G</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAMARITAN HOSPITAL</InvolvedTransportedTo>
<InvolvedInjuryDesc1>SWELLING AND BRUISING ON ARMS / DIZZINESS</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger />
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>2</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240072</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>38</Age>
<Sex>M</Sex>
<BirthDateYear>1981</BirthDateYear>
<BirthDateMonth>12</BirthDateMonth>
<BirthDateDay>18</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>G</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #3</InvolvedTransportedTo>
<InvolvedInjuryDesc1>NECK AND CHEST PAIN</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>3</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240073</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue />
<Age>6</Age>
<Sex>M</Sex>
<BirthDateYear>2013</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>05</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>Q</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #4</InvolvedTransportedTo>
<InvolvedInjuryDesc1>BLOODY MOUTH</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>4</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240075</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>3</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue />
<Age>3</Age>
<Sex>M</Sex>
<BirthDateYear>2016</BirthDateYear>
<BirthDateMonth>08</BirthDateMonth>
<BirthDateDay>11</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17 />
<InvolvedSafetyEquipmentUsed-Box-19 />
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #6</InvolvedTransportedTo>
<InvolvedInjuryDesc1>ABDOMINAL PAIN</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<Diagram />
<Diagram2 />
<AccidentDescriptionText />
<Latitude-AllPages />
<Longitude-AllPages />
<SpecialCondition />
<CountyTrans-AllPages>SANTA CLARA COUNTY</CountyTrans-AllPages>
<AccidentWeekday-1-AllPages />
<AccidentWeekday-2-AllPages />
<AccidentWeekday-3-AllPages />
<AccidentWeekday-4-AllPages />
<AccidentWeekday-5-AllPages />
<AccidentWeekday-6-AllPages />
<AccidentWeekday-7-AllPages>[X]</AccidentWeekday-7-AllPages>
<PropertyOwnerName-AllPages />
<PropertyOwnerAddress-AllPages />
<PropertyDamage-AllPages />
<OtherAssociatedFactorsKText />
<MovementProcedingCollisionRText />
<OtherAssociatedFactorsMText />
<TowAwayYes-AllPages>1</TowAwayYes-AllPages>
<OtherAssociatedFactorsKCitedYesNo />
<JudicialDistrict-AllPages>43470</JudicialDistrict-AllPages>
<PreparersDate-AllPages>02-01-2020</PreparersDate-AllPages>
<ApprovedBy-AllPages />
<Description>
<NarrativeText>
<LINE>SUMMARY: On 2-1-20 at about 1547 hrs, emergency services responded to a two-vehicle collision on</LINE>
<LINE>Branham Ln at Pinmore Dr. Upon police arrival both vehicles were disabled in the roadway. </LINE>
<LINE>Fire/medics assessed all parties for injuries.</LINE>
<LINE />
<LINE>STATEMENTS:</LINE>
<LINE />
<LINE> D-1(White) identified herself as the drive of V-1(Toyt) with her CDL. D-1 stated that she was</LINE>
<LINE>making a left turn from the Safeway parking lot to eastbound Branham Ln when she was abruptly struck</LINE>
<LINE>by V-2(Volk). D-1 said she did not see V-2 approached and speculated that D-2 may have been</LINE>
<LINE>speeding. D-1 had a complaint of pain across her neck but declined medical transport.</LINE>
<LINE />
<LINE> D-2(Joo) identified himself as the driver of V-2(Volk) with his CDL. D-2 stated that he was</LINE>
<LINE>simply driving approximately 35 mph in the #1 lane of westbound Branham Ln when V-1(Toyt) abruptly</LINE>
<LINE>pulled out in front of him. D-2 stated that he swerved to avoid colliding, however, he had little</LINE>
<LINE>time to react.</LINE>
<LINE />
<LINE>INJURIES: All 4 occupants of V-2 were transported by ambulance for further assessment of minor to</LINE>
<LINE>moderate injuries.</LINE>
<LINE />
<LINE>CAUSE: D-1(White) caused the collision when she failed to yield to all vehicles approaching from</LINE>
<LINE>opposite direction close enough to be a hazard during any part of the turn in violation of 21801(a)</LINE>
<LINE>VC.</LINE>
</NarrativeText>
</Description>
<PreparersName-AllPages>CRAIG,CHRISTOPHER J (3961)</PreparersName-AllPages>
<AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
<PreparersID-AllPages>3961</PreparersID-AllPages>
<NCIC-AllPages>4313</NCIC-AllPages>
<ApprovedDate-AllPages />
<chkCollOther-AllPages />
<chkTypeSupp_BAUpdate-AllPages />
<chkTypeSupp_HazMat-AllPages />
<chkTypeSupp_Fatal-AllPages />
<chkTypeSupp_SBus-AllPages />
<chkTypeSupp_HitandRun-AllPages />
<chkTypeSupp_Other-AllPages />
<Other_text-AllPages />
<Coll_Other_text-AllPages />
<CitationNumber-AllPages />
<Beat-AllPages>A1</Beat-AllPages>
<chkStateHywRelated-AllPages />
<chkNarrative-AllPages>1</chkNarrative-AllPages>
<Municipality-AllPages>SAN JOSE</Municipality-AllPages>
<Subject-AllPages />
<SpecialCondition-AllPages />
<NumberInjured-AllPages>4</NumberInjured-AllPages>
<NumberKilled-AllPages />
<HitAndRunFelony-AllPages />
<HitAndRunMisdemeanor-AllPages />
<PhotographsBy-AllPages />
<PhotographsNone-AllPages />
<StateHywRelYesNo-AllPages>0</StateHywRelYesNo-AllPages>
<MilePostInfo1-AllPages />
<MilePostInfo2-AllPages />
<MilePostInfo3-AllPages />
<AtIntersection1-AllPages />
<AtIntersection2-AllPages />
<AtIntersection3-AllPages />
<DispatchNotifiedY-AllPages />
<DispatchNotifiedN-AllPages />
<DispatchNotifiedNA-AllPages />
<ProperyDamageNotifiedYesNo-AllPages />
<AccidentDescriptionText-AllPages />
<AtIntersectionWith-AllPages />
<PrimaryCollisionA-AllPages>1</PrimaryCollisionA-AllPages>
<PrimaryCollisionB-AllPages />
<PrimaryCollisionC-AllPages />
<PrimaryCollisionD-AllPages />
<VCSectionViolatedText-AllPages>21801(a) VC</VCSectionViolatedText-AllPages>
<PrimaryCollisionBText-AllPages />
<VCSectionViolatedCitedYes-AllPages>0</VCSectionViolatedCitedYes-AllPages>
<WeatherA-AllPages>1</WeatherA-AllPages>
<WeatherB-AllPages />
<WeatherC-AllPages />
<WeatherD-AllPages />
<WeatherE-AllPages />
<WeatherF-AllPages />
<WeatherG-AllPages />
<Weather_visibility-AllPages />
<WeatherOther-AllPages />
<LightingA-AllPages>1</LightingA-AllPages>
<LightingB-AllPages />
<LightingC-AllPages />
<LightingD-AllPages />
<LightingE-AllPages />
<RoadwaySurfaceA-AllPages>1</RoadwaySurfaceA-AllPages>
<RoadwaySurfaceB-AllPages />
<RoadwaySurfaceC-AllPages />
<RoadwaySurfaceD-AllPages />
<RoadwayConditionsA-AllPages />
<RoadwayConditionsB-AllPages />
<RoadwayConditionsC-AllPages />
<RoadwayConditionsD-AllPages />
<RoadwayConditionsE-AllPages />
<RoadwayConditionsF-AllPages />
<RoadwayConditionsG-AllPages />
<RoadwayConditionsH-AllPages>1</RoadwayConditionsH-AllPages>
<TrafficControlDevicesA-AllPages />
<TrafficControlDevicesB-AllPages />
<TrafficControlDevicesC-AllPages />
<TrafficControlDevicesD-AllPages>1</TrafficControlDevicesD-AllPages>
<TypeOfCollisionA-AllPages />
<TypeOfCollisionB-AllPages />
<TypeOfCollisionC-AllPages />
<TypeOfCollisionD-AllPages>1</TypeOfCollisionD-AllPages>
<TypeOfCollisionE-AllPages />
<TypeOfCollisionF-AllPages />
<TypeOfCollisionG-AllPages />
<TypeOfCollisionH-AllPages />
<MotorVehicleInvolvedWithA-AllPages />
<MotorVehicleInvolvedWithB-AllPages />
<MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
<MotorVehicleInvolvedWithD-AllPages />
<MotorVehicleInvolvedWithE-AllPages />
<MotorVehicleInvolvedWithF-AllPages />
<MotorVehicleInvolvedWithG-AllPages />
<MotorVehicleInvolvedWithH-AllPages />
<MotorVehicleInvolvedWithI-AllPages />
<MotorVehicleInvolvedWithJ-AllPages />
<PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
<PedestrianActionB-AllPages />
<PedestrianActionC-AllPages />
<PedestrianActionD-AllPages />
<PedestrianActionE-AllPages />
<PedestrianActionF-AllPages />
<PedestrianActionG-AllPages />
<MotorVehicleInvolvedWithHText-AllPages />
<MotorVehicleInvolvedWithIText-AllPages />
<MotorVehicleInvolvedWithJText-AllPages />
<CollisionOccurredOn2-AllPages>PINMORE DR</CollisionOccurredOn2-AllPages>
<CollisionOccurredOn1-AllPages>BRANHAM LN</CollisionOccurredOn1-AllPages>
<RoadwayCGText-AllPages />
<TypeOfCollisionHText-AllPages />
<OtherAssocFactorsFCode-Box-22 />
<CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
<Time-AllPages>1547</Time-AllPages>
</agency>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris H (chris.sjpd@gmail.com) writes:

WITH
(
AccidentReportNumber-AllPages varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
InjuredWas_driver int '/involvedPeople',
PedestrianActionA-AllPages int,
MotorVehicleInvolvedWithC-AllPages int,
CollisionOccurredDate-AllPages date,
Time-AllPages int
)

Here is the error:
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '-'.

The name you have to the left is a column name, and must follow the regular syntax for identifiers. Hyphen is not a permitted character in an
identifier.

You need to wrap these names in double quotes or bracket. These delimiters permits you to have about any column name.

For instance:

[PedestrianActionA-AllPages] int,
"MotorVehicleInvolvedWithC-AllPages" int,

"" and[] are equivalent. "" is the ANSI standard. [] is the Microsoft way.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, June 10, 2020 at 12:55:06 PM UTC-7, Erland Sommarskog wrote:

chris H (chris.sjpd@gmail.com) writes:

WITH
(
AccidentReportNumber-AllPages varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
InjuredWas_driver int '/involvedPeople',
PedestrianActionA-AllPages int,
MotorVehicleInvolvedWithC-AllPages int,
CollisionOccurredDate-AllPages date,
Time-AllPages int
)

Here is the error:
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '-'.

The name you have to the left is a column name, and must follow the regular syntax for identifiers. Hyphen is not a permitted character in an
identifier.

You need to wrap these names in double quotes or bracket. These delimiters permits you to have about any column name.

For instance:

[PedestrianActionA-AllPages] int,
"MotorVehicleInvolvedWithC-AllPages" int,

"" and[] are equivalent. "" is the ANSI standard. [] is the Microsoft way.

Hi Erland,

Thank you for the suggestion. The codes do not error out anymore; however, it does not give me the value. Am I missing something?

SELECT *
FROM OPENXML(@hDoc, '/Agency', 1)
WITH
(
[AccidentReportNumber-AllPages] varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
"InjuredWas_driver" int '/involvedPeople',
[PedestrianActionA-AllPages] int,
[MotorVehicleInvolvedWithC-AllPages] int,
[CollisionOccurredDate-AllPages] date,
[Time-AllPages] int
)

Really appreciate your helps.
Sincerely,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris H (chris.sjpd@gmail.com) writes:

That is a complete xml file. When I doubleclick to open it, it does not have root tag.

I would say it's not an XML file then. An XML file has exactly a single root tag. SQL Server supports repeated root tags, but there are no root tags at all here.

I get this error from sp_xml_preparedocument:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 0]
The error description is 'Only one top level element is allowed in an XML document.'.


DECLARE @x xml =
' <AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
<AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA, 95118</AddressFullText>
<InjuredWas_driver>1</InjuredWas_driver>
<PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
<MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
<CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
<Time-AllPages>1547</Time-AllPages>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @x


SELECT *
FROM OPENXML(@hDoc, '/', 1)
WITH
(
[AccidentReportNumber-AllPages] varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
"InjuredWas_driver" int '/involvedPeople',
[PedestrianActionA-AllPages] int,
[MotorVehicleInvolvedWithC-AllPages] int,
[CollisionOccurredDate-AllPages] date,
[Time-AllPages] int
)

EXEC sp_xml_removedocument @hDoc OUTPUT

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, June 10, 2020 at 2:42:09 PM UTC-7, Erland Sommarskog wrote:

chris H (chris.sjpd@gmail.com) writes:

Thank you for the suggestion. The codes do not error out anymore;
however, it does not give me the value. Am I missing something?

SELECT *
FROM OPENXML(@hDoc, '/Agency', 1)

What does your full XML document look like? What you posted, is just a bunch of unrelated element with no root tag. I assume the full document is longer.

Hi Erland,

That is a complete xml file. When I doubleclick to open it, it does not have root tag.

Thanks,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris H (chris.sjpd@gmail.com) writes:

Thank you for the suggestion. The codes do not error out anymore;
however, it does not give me the value. Am I missing something?

SELECT *
FROM OPENXML(@hDoc, '/Agency', 1)

What does your full XML document look like? What you posted, is just a bunch
of unrelated element with no root tag. I assume the full document is longer.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, June 10, 2020 at 3:15:07 PM UTC-7, Erland Sommarskog wrote:

chris H (chris.sjpd@gmail.com) writes:

That is a complete xml file. When I doubleclick to open it, it does not have root tag.

I would say it's not an XML file then. An XML file has exactly a single root tag. SQL Server supports repeated root tags, but there are no root tags at all here.

I get this error from sp_xml_preparedocument:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 0]
The error description is 'Only one top level element is allowed in an XML document.'.

DECLARE @x xml =
' <AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
<AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA, 95118</AddressFullText>
<InjuredWas_driver>1</InjuredWas_driver>
<PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
<MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
<CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
<Time-AllPages>1547</Time-AllPages>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @x

SELECT *
FROM OPENXML(@hDoc, '/', 1)
WITH
(
[AccidentReportNumber-AllPages] varchar(20),
AddressFullText varchar(100) '/trafficunit/driver',
"InjuredWas_driver" int '/involvedPeople',
[PedestrianActionA-AllPages] int,
[MotorVehicleInvolvedWithC-AllPages] int,
[CollisionOccurredDate-AllPages] date,
[Time-AllPages] int
)

EXEC sp_xml_removedocument @hDoc OUTPUT

This is all I have to work with :-( The xml file is coming from an application which combines some columns from db & others from the xml form. I have spent a lot of time to get it work because we only need data in a few columns


<agency xmlns="" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/
envelope" xmlns:fjs1="http://www.versaterm.com/rms/ns/RMS_7.2" xmlns:fjs2="http://www.versaterm.com/rms/types/RMS_7.2" formLock="off" project_name="mva_california" form_version="065" form_path="site/version/" formname="vtm_california_mva_v1">
<PageNum />
<Pages />
<TrafficUnit>
<TrafficUnit_Number>1</TrafficUnit_Number>
<Driver>
<BlankOption />
<TrafficUnit_Number />
<ID_Num>442240070</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<AddressFullText>1554 GLENCREST CT,SAN JOSE, CA, 95118</AddressFullText>
<AddressStreetText>1554 GLENCREST CT</AddressStreetText>
<AddressCityStateZip>SAN JOSE, CA, 95118</AddressCityStateZip>
<City>SAN JOSE</City>
<State>CA</State>
<Zip>95118</Zip>
<StateOfIssue>CA</StateOfIssue>
<LicenseClass>01</LicenseClass>
<Sex>F</Sex>
<Hair>BRO</Hair>
<Eyes>BRO</Eyes>
<Height>5'06</Height>
<Weight>140</Weight>
<Race>W</Race>
<DateOfBirth>09-20-1985</DateOfBirth>
<BirthDateYear>1985</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>20</BirthDateDay>
<Age>34</Age>
<ChargeDescriptionText />
</Driver>
<Vehicle>
<BlankOption />
<ID_Num>211222</ID_Num>
<RoleTrans>Involved (ACCIDENTS&amp;STR CHKS)</RoleTrans>
<RoleNumber>1</RoleNumber>
<ModelYear>2020</ModelYear>
<Make>TOYT</Make>
<Model>SNA</Model>
<Style>4H</Style>
<Colour>WHI/</Colour>
<StateOfIssue>CA</StateOfIssue>
<AddressFullText />
<CityState />
<Zip />
<VehicleType>1</VehicleType>
<NumberOfOccupants />
<InsuranceName>AAA</InsuranceName>
<InsuranceExpiryDate />
</Vehicle>
<MovementProcedingCollisionB />
<MovementProcedingCollisionC />
<MovementProcedingCollisionA />
<MovementProcedingCollisionE>1</MovementProcedingCollisionE>
<VehicleOwner>
<BlankOption />
<ID_Num>442240070</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<Seat_position />
<AddressFullText>1554 GLENCREST CT</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>34</Age>
<Sex>F</Sex>
<BirthDateYear>1985</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>20</BirthDateDay>
</VehicleOwner>
<NameSameAsDriver />
<AddrSameAsDriver />
<TrafficUnitDriverOtherCheck />
<TrafficUnitDriverBikeCheck />
<TrafficUnitDriverParkedCheck />
<TrafficUnitPedestrianCheck />
<TrafficUnitDriverCheck>1</TrafficUnitDriverCheck>
<DispositionVehicleByOfficer />
<DispositionVehicleByDriver>1</DispositionVehicleByDriver>
<DispositionVehicleByOther />
<PriorMechanicalDefectsNone />
<PriorMechanicalDefectsNarr />
<DescribeVehDamageUnk />
<DescribeVehDamageNone />
<DescribeVehDamageMinor />
<DescribeVehDamageMod />
<DescribeVehDamageMajor>1</DescribeVehDamageMajor>
<DescribeVehDamageRollOver />
<Damage_14 />
<Damage_13 />
<Damage_11>1</Damage_11>
<Damage_9>1</Damage_9>
<Damage_7 />
<Damage_4 />
<Damage_3 />
<Damage_2 />
<Damage_12 />
<Damage_1 />
<Damage_10>1</Damage_10>
<Damage_8 />
<Damage_6 />
<Damage_5 />
<VehicleType1 />
<VehicleType2 />
<SpecialInformationA />
<SpecialInformationB />
<SpecialInformationC />
<SpecialInformationE />
<SpecialInformationD />
<SpecialInformationF />
<SpecialInformationG />
<SpecialInformationI />
<SpecialInformationH />
<SpecialInformationJ />
<SpecialInformationK />
<SpecialInformationL />
<SpecialInformationM />
<SpecialInformationN />
<SpecialInformationO />
<OtherAssociatedFactorsC />
<OtherAssociatedFactorsB />
<OtherAssociatedFactorsA />
<OtherAssociatedFactorsE />
<OtherAssociatedFactorsF />
<OtherAssociatedFactorsG />
<OtherAssociatedFactorsH />
<OtherAssociatedFactorsI />
<OtherAssociatedFactorsJ />
<OtherAssociatedFactorsK />
<OtherAssociatedFactorsL />
<OtherAssociatedFactorsM />
<OtherAssociatedFactorsN />
<OtherAssociatedFactorsO />
<MovementProcedingCollisionD />
<MovementProcedingCollisionF />
<MovementProcedingCollisionG />
<MovementProcedingCollisionH />
<MovementProcedingCollisionI />
<MovementProcedingCollisionJ />
<MovementProcedingCollisionK />
<MovementProcedingCollisionL />
<MovementProcedingCollisionM />
<MovementProcedingCollisionN />
<MovementProcedingCollisionO />
<MovementProcedingCollisionP />
<MovementProcedingCollisionQ />
<MovementProcedingCollisionR />
<SobrietyDrugPhysicalA>1</SobrietyDrugPhysicalA>
<SobrietyDrugPhysicalC />
<SobrietyDrugPhysicalB />
<SobrietyDrugPhysicalE />
<SobrietyDrugPhysicalD />
<SobrietyDrugPhysicalF />
<SobrietyDrugPhysicalG />
<SobrietyDrugPhysicalH />
<SobrietyDrugPhysicalI />
<OnDutyEMV />
<DriverAirBag-Box-16>M</DriverAirBag-Box-16>
<DriverSafetyEquipmentUsed-Box-18>G</DriverSafetyEquipmentUsed-Box-18>
<Direction-Box-5>SE</Direction-Box-5>
<OnStreetOrHighway>BRANHAM LN</OnStreetOrHighway>
<SpeedLimit>35</SpeedLimit>
<DispositionText />
<OtherAssociatedFactorsAText />
<OtherAssociatedFactorsACitedYesNO />
<CAL_T />
<MC_MX />
<CA />
<DOT />
<TCP_PSC />
</TrafficUnit>
<TrafficUnit>
<TrafficUnit_Number>2</TrafficUnit_Number>
<Driver>
<BlankOption />
<TrafficUnit_Number />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<AddressFullText>1493 CRESTHAVEN LN,SAN JOSE, CA, 95118</AddressFullText>
<AddressStreetText>1493 CRESTHAVEN LN</AddressStreetText>
<AddressCityStateZip>SAN JOSE, CA, 95118</AddressCityStateZip>
<City>SAN JOSE</City>
<State>CA</State>
<Zip>95118</Zip>
<StateOfIssue>CA</StateOfIssue>
<LicenseClass>01</LicenseClass>
<Sex>M</Sex>
<Hair>BLK</Hair>
<Eyes>BRO</Eyes>
<Height>5'6</Height>
<Weight>138</Weight>
<Race>A</Race>
<DateOfBirth>09-29-1984</DateOfBirth>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
<Age>35</Age>
<ChargeDescriptionText />
</Driver>
<Vehicle>
<BlankOption />
<ID_Num>211223</ID_Num>
<RoleTrans>Involved (ACCIDENTS&amp;STR CHKS)</RoleTrans>
<RoleNumber>2</RoleNumber>
<ModelYear>2015</ModelYear>
<Make>VOLK</Make>
<Model>TGN</Model>
<Style>4H</Style>
<Colour>GRY/</Colour>
<StateOfIssue>CA</StateOfIssue>
<AddressFullText />
<CityState />
<Zip />
<VehicleType>1</VehicleType>
<NumberOfOccupants />
<InsuranceName>GEICO</InsuranceName>
<InsuranceExpiryDate />
</Vehicle>
<MovementProcedingCollisionB>1</MovementProcedingCollisionB>
<MovementProcedingCollisionC />
<MovementProcedingCollisionA />
<MovementProcedingCollisionE />
<VehicleOwner>
<BlankOption />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>35</Age>
<Sex>M</Sex>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
</VehicleOwner>
<NameSameAsDriver />
<AddrSameAsDriver />
<TrafficUnitDriverOtherCheck />
<TrafficUnitDriverBikeCheck />
<TrafficUnitDriverParkedCheck />
<TrafficUnitPedestrianCheck />
<TrafficUnitDriverCheck>1</TrafficUnitDriverCheck>
<DispositionVehicleByOfficer />
<DispositionVehicleByDriver>1</DispositionVehicleByDriver>
<DispositionVehicleByOther />
<PriorMechanicalDefectsNone>1</PriorMechanicalDefectsNone>
<PriorMechanicalDefectsNarr />
<DescribeVehDamageUnk />
<DescribeVehDamageNone />
<DescribeVehDamageMinor />
<DescribeVehDamageMod />
<DescribeVehDamageMajor>1</DescribeVehDamageMajor>
<DescribeVehDamageRollOver />
<Damage_14 />
<Damage_13 />
<Damage_11 />
<Damage_9>1</Damage_9>
<Damage_7 />
<Damage_4 />
<Damage_3 />
<Damage_2 />
<Damage_12 />
<Damage_1 />
<Damage_10>1</Damage_10>
<Damage_8>1</Damage_8>
<Damage_6 />
<Damage_5 />
<VehicleType1 />
<VehicleType2 />
<SpecialInformationA />
<SpecialInformationB />
<SpecialInformationC />
<SpecialInformationE />
<SpecialInformationD />
<SpecialInformationF />
<SpecialInformationG />
<SpecialInformationI />
<SpecialInformationH />
<SpecialInformationJ />
<SpecialInformationK />
<SpecialInformationL />
<SpecialInformationM />
<SpecialInformationN />
<SpecialInformationO />
<OtherAssociatedFactorsC />
<OtherAssociatedFactorsB />
<OtherAssociatedFactorsA />
<OtherAssociatedFactorsE />
<OtherAssociatedFactorsF />
<OtherAssociatedFactorsG />
<OtherAssociatedFactorsH />
<OtherAssociatedFactorsI />
<OtherAssociatedFactorsJ />
<OtherAssociatedFactorsK />
<OtherAssociatedFactorsL />
<OtherAssociatedFactorsM />
<OtherAssociatedFactorsN />
<OtherAssociatedFactorsO />
<MovementProcedingCollisionD />
<MovementProcedingCollisionF />
<MovementProcedingCollisionG />
<MovementProcedingCollisionH />
<MovementProcedingCollisionI />
<MovementProcedingCollisionJ />
<MovementProcedingCollisionK />
<MovementProcedingCollisionL />
<MovementProcedingCollisionM />
<MovementProcedingCollisionN />
<MovementProcedingCollisionO />
<MovementProcedingCollisionP />
<MovementProcedingCollisionQ />
<MovementProcedingCollisionR />
<SobrietyDrugPhysicalA>1</SobrietyDrugPhysicalA>
<SobrietyDrugPhysicalC />
<SobrietyDrugPhysicalB />
<SobrietyDrugPhysicalE />
<SobrietyDrugPhysicalD />
<SobrietyDrugPhysicalF />
<SobrietyDrugPhysicalG />
<SobrietyDrugPhysicalH />
<SobrietyDrugPhysicalI />
<OnDutyEMV />
<DriverAirBag-Box-16>L</DriverAirBag-Box-16>
<DriverSafetyEquipmentUsed-Box-18>G</DriverSafetyEquipmentUsed-Box-18>
<Direction-Box-5>W</Direction-Box-5>
<OnStreetOrHighway>BRANHAM LN</OnStreetOrHighway>
<SpeedLimit>35</SpeedLimit>
<DispositionText />
<OtherAssociatedFactorsAText />
<OtherAssociatedFactorsACitedYesNO />
<CAL_T />
<MC_MX />
<CA />
<DOT />
<TCP_PSC />
</TrafficUnit>
<ReportingDistrict-AllPages>SOUTHERN</ReportingDistrict-AllPages>
<InvolvedPeople>
<InvolvedPeople_Number>1</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240071</ID_Num>
<RoleTrans>Drv(ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>35</Age>
<Sex>M</Sex>
<BirthDateYear>1984</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>29</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury>1</ExtentOfInjury_OtherVisInjury>
<ExtentOfInjury_ComplaintOfPain>1</ExtentOfInjury_ComplaintOfPain>
<InjuredWas_driver>1</InjuredWas_driver>
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>G</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAMARITAN HOSPITAL</InvolvedTransportedTo>
<InvolvedInjuryDesc1>SWELLING AND BRUISING ON ARMS / DIZZINESS</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger />
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>2</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240072</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>1</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue>CA</ProvStateOfIssue>
<Age>38</Age>
<Sex>M</Sex>
<BirthDateYear>1981</BirthDateYear>
<BirthDateMonth>12</BirthDateMonth>
<BirthDateDay>18</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>G</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #3</InvolvedTransportedTo>
<InvolvedInjuryDesc1>NECK AND CHEST PAIN</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>3</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240073</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>2</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue />
<Age>6</Age>
<Sex>M</Sex>
<BirthDateYear>2013</BirthDateYear>
<BirthDateMonth>09</BirthDateMonth>
<BirthDateDay>05</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17>L</InvolvedAirBag-Box-17>
<InvolvedSafetyEquipmentUsed-Box-19>Q</InvolvedSafetyEquipmentUsed-Box-19>
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #4</InvolvedTransportedTo>
<InvolvedInjuryDesc1>BLOODY MOUTH</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<InvolvedPeople>
<InvolvedPeople_Number>4</InvolvedPeople_Number>
<Person>
<BlankOption />
<ID_Num>442240075</ID_Num>
<RoleTrans>Inj (ACC)</RoleTrans>
<RoleNumber>3</RoleNumber>
<Seat_position />
<AddressFullText>1493 CRESTHAVEN LN</AddressFullText>
<CityState>SAN JOSE, CA</CityState>
<Zip>95118</Zip>
<ProvStateOfIssue />
<Age>3</Age>
<Sex>M</Sex>
<BirthDateYear>2016</BirthDateYear>
<BirthDateMonth>08</BirthDateMonth>
<BirthDateDay>11</BirthDateDay>
</Person>
<Describe_Injuries />
<InvolvedInjuryDesc />
<Witness_Only />
<Passenger_Only />
<ExtentOfInjury_Fatal />
<ExtentOfInjury_Severe />
<ExtentOfInjury_OtherVisInjury />
<ExtentOfInjury_ComplaintOfPain />
<InjuredWas_driver />
<InjuredWas_Pedestrian />
<InjuredWas_Bicyclist />
<InjuredWas_Other />
<Victim_ViolentCrimeNotified />
<PartyNumber>2</PartyNumber>
<InvolvedAirBag-Box-17 />
<InvolvedSafetyEquipmentUsed-Box-19 />
<InvolvedEjection-Box-20 />
<InvolvedTransportedBy>EMS</InvolvedTransportedBy>
<InvolvedTransportedTo>GOOD SAM SEAT POS #6</InvolvedTransportedTo>
<InvolvedInjuryDesc1>ABDOMINAL PAIN</InvolvedInjuryDesc1>
<InvolvedInjuryDesc2 />
<InvolvedInjuryDesc3 />
<Witness_Unit />
<InjuredWas_passenger>1</InjuredWas_passenger>
</InvolvedPeople>
<Diagram />
<Diagram2 />
<AccidentDescriptionText />
<Latitude-AllPages />
<Longitude-AllPages />
<SpecialCondition />
<CountyTrans-AllPages>SANTA CLARA COUNTY</CountyTrans-AllPages>
<AccidentWeekday-1-AllPages />
<AccidentWeekday-2-AllPages />
<AccidentWeekday-3-AllPages />
<AccidentWeekday-4-AllPages />
<AccidentWeekday-5-AllPages />
<AccidentWeekday-6-AllPages />
<AccidentWeekday-7-AllPages>[X]</AccidentWeekday-7-AllPages>
<PropertyOwnerName-AllPages />
<PropertyOwnerAddress-AllPages />
<PropertyDamage-AllPages />
<OtherAssociatedFactorsKText />
<MovementProcedingCollisionRText />
<OtherAssociatedFactorsMText />
<TowAwayYes-AllPages>1</TowAwayYes-AllPages>
<OtherAssociatedFactorsKCitedYesNo />
<JudicialDistrict-AllPages>43470</JudicialDistrict-AllPages>
<PreparersDate-AllPages>02-01-2020</PreparersDate-AllPages>
<ApprovedBy-AllPages />
<Description>
<NarrativeText>
<LINE>SUMMARY: On 2-1-20 at about 1547 hrs, emergency services responded to a two-vehicle collision on</LINE>
<LINE>Branham Ln at Pinmore Dr. Upon police arrival both vehicles were disabled in the roadway. </LINE>
<LINE>Fire/medics assessed all parties for injuries.</LINE>
<LINE />
<LINE>STATEMENTS:</LINE>
<LINE />
<LINE> D-1(White) identified herself as the drive of V-1(Toyt) with her CDL. D-1 stated that she was</LINE>
<LINE>making a left turn from the Safeway parking lot to eastbound Branham Ln when she was abruptly struck</LINE>
<LINE>by V-2(Volk). D-1 said she did not see V-2 approached and speculated that D-2 may have been</LINE>
<LINE>speeding. D-1 had a complaint of pain across her neck but declined medical transport.</LINE>
<LINE />
<LINE> D-2(Joo) identified himself as the driver of V-2(Volk) with his CDL. D-2 stated that he was</LINE>
<LINE>simply driving approximately 35 mph in the #1 lane of westbound Branham Ln when V-1(Toyt) abruptly</LINE>
<LINE>pulled out in front of him. D-2 stated that he swerved to avoid colliding, however, he had little</LINE>
<LINE>time to react.</LINE>
<LINE />
<LINE>INJURIES: All 4 occupants of V-2 were transported by ambulance for further assessment of minor to</LINE>
<LINE>moderate injuries.</LINE>
<LINE />
<LINE>CAUSE: D-1(White) caused the collision when she failed to yield to all vehicles approaching from</LINE>
<LINE>opposite direction close enough to be a hazard during any part of the turn in violation of 21801(a)</LINE>
<LINE>VC.</LINE>
</NarrativeText>
</Description>
<PreparersName-AllPages>CRAIG,CHRISTOPHER J (3961)</PreparersName-AllPages>
<AccidentReportNumber-AllPages>2020-200320523</AccidentReportNumber-AllPages>
<PreparersID-AllPages>3961</PreparersID-AllPages>
<NCIC-AllPages>4313</NCIC-AllPages>
<ApprovedDate-AllPages />
<chkCollOther-AllPages />
<chkTypeSupp_BAUpdate-AllPages />
<chkTypeSupp_HazMat-AllPages />
<chkTypeSupp_Fatal-AllPages />
<chkTypeSupp_SBus-AllPages />
<chkTypeSupp_HitandRun-AllPages />
<chkTypeSupp_Other-AllPages />
<Other_text-AllPages />
<Coll_Other_text-AllPages />
<CitationNumber-AllPages />
<Beat-AllPages>A1</Beat-AllPages>
<chkStateHywRelated-AllPages />
<chkNarrative-AllPages>1</chkNarrative-AllPages>
<Municipality-AllPages>SAN JOSE</Municipality-AllPages>
<Subject-AllPages />
<SpecialCondition-AllPages />
<NumberInjured-AllPages>4</NumberInjured-AllPages>
<NumberKilled-AllPages />
<HitAndRunFelony-AllPages />
<HitAndRunMisdemeanor-AllPages />
<PhotographsBy-AllPages />
<PhotographsNone-AllPages />
<StateHywRelYesNo-AllPages>0</StateHywRelYesNo-AllPages>
<MilePostInfo1-AllPages />
<MilePostInfo2-AllPages />
<MilePostInfo3-AllPages />
<AtIntersection1-AllPages />
<AtIntersection2-AllPages />
<AtIntersection3-AllPages />
<DispatchNotifiedY-AllPages />
<DispatchNotifiedN-AllPages />
<DispatchNotifiedNA-AllPages />
<ProperyDamageNotifiedYesNo-AllPages />
<AccidentDescriptionText-AllPages />
<AtIntersectionWith-AllPages />
<PrimaryCollisionA-AllPages>1</PrimaryCollisionA-AllPages>
<PrimaryCollisionB-AllPages />
<PrimaryCollisionC-AllPages />
<PrimaryCollisionD-AllPages />
<VCSectionViolatedText-AllPages>21801(a) VC</VCSectionViolatedText-AllPages>
<PrimaryCollisionBText-AllPages />
<VCSectionViolatedCitedYes-AllPages>0</VCSectionViolatedCitedYes-AllPages>
<WeatherA-AllPages>1</WeatherA-AllPages>
<WeatherB-AllPages />
<WeatherC-AllPages />
<WeatherD-AllPages />
<WeatherE-AllPages />
<WeatherF-AllPages />
<WeatherG-AllPages />
<Weather_visibility-AllPages />
<WeatherOther-AllPages />
<LightingA-AllPages>1</LightingA-AllPages>
<LightingB-AllPages />
<LightingC-AllPages />
<LightingD-AllPages />
<LightingE-AllPages />
<RoadwaySurfaceA-AllPages>1</RoadwaySurfaceA-AllPages>
<RoadwaySurfaceB-AllPages />
<RoadwaySurfaceC-AllPages />
<RoadwaySurfaceD-AllPages />
<RoadwayConditionsA-AllPages />
<RoadwayConditionsB-AllPages />
<RoadwayConditionsC-AllPages />
<RoadwayConditionsD-AllPages />
<RoadwayConditionsE-AllPages />
<RoadwayConditionsF-AllPages />
<RoadwayConditionsG-AllPages />
<RoadwayConditionsH-AllPages>1</RoadwayConditionsH-AllPages>
<TrafficControlDevicesA-AllPages />
<TrafficControlDevicesB-AllPages />
<TrafficControlDevicesC-AllPages />
<TrafficControlDevicesD-AllPages>1</TrafficControlDevicesD-AllPages>
<TypeOfCollisionA-AllPages />
<TypeOfCollisionB-AllPages />
<TypeOfCollisionC-AllPages />
<TypeOfCollisionD-AllPages>1</TypeOfCollisionD-AllPages>
<TypeOfCollisionE-AllPages />
<TypeOfCollisionF-AllPages />
<TypeOfCollisionG-AllPages />
<TypeOfCollisionH-AllPages />
<MotorVehicleInvolvedWithA-AllPages />
<MotorVehicleInvolvedWithB-AllPages />
<MotorVehicleInvolvedWithC-AllPages>1</MotorVehicleInvolvedWithC-AllPages>
<MotorVehicleInvolvedWithD-AllPages />
<MotorVehicleInvolvedWithE-AllPages />
<MotorVehicleInvolvedWithF-AllPages />
<MotorVehicleInvolvedWithG-AllPages />
<MotorVehicleInvolvedWithH-AllPages />
<MotorVehicleInvolvedWithI-AllPages />
<MotorVehicleInvolvedWithJ-AllPages />
<PedestrianActionA-AllPages>1</PedestrianActionA-AllPages>
<PedestrianActionB-AllPages />
<PedestrianActionC-AllPages />
<PedestrianActionD-AllPages />
<PedestrianActionE-AllPages />
<PedestrianActionF-AllPages />
<PedestrianActionG-AllPages />
<MotorVehicleInvolvedWithHText-AllPages />
<MotorVehicleInvolvedWithIText-AllPages />
<MotorVehicleInvolvedWithJText-AllPages />
<CollisionOccurredOn2-AllPages>PINMORE DR</CollisionOccurredOn2-AllPages>
<CollisionOccurredOn1-AllPages>BRANHAM LN</CollisionOccurredOn1-AllPages>
<RoadwayCGText-AllPages />
<TypeOfCollisionHText-AllPages />
<OtherAssocFactorsFCode-Box-22 />
<CollisionOccurredDate-AllPages>02-01-2020</CollisionOccurredDate-AllPages>
<Time-AllPages>1547</Time-AllPages>
</agency>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris H (chris.sjpd@gmail.com) writes:

This is all I have to work with :-( The xml file is coming from an application which combines some columns from db & others from the xml
form. I have spent a lot of time to get it work because we only need
data in a few columns

What you post now is something completely different. That is a complete
XML document, and indeed it is possible to get data out of it. However,
since it is so big, and has so many tags, it takes a while to get all
you need. Here is a start:

SELECT Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]',
'varchar(20)') AS [CollisionOccurredDate-AllPage],
Driver.d.value('(AddressFullText/text())[1]',
'nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver')
AS Driver(d)

You may note that there is no OPENXML. OPENXML was introduced in SQL 2000.
With SQL 2005 we got XQuery, which is generally considered better to
get data out from XML.

The nodees function gives you nodes in the document, and the first is
the root node, and the CollisionOccurredDate is directly below the
root node.

The AddressFullText node on the other hand, is inside the Driver node which
is inside the TrafficUnit node wihch is under the root node. On top of
all there is more than one TrafficUnit node, and it appears you want
the second.

The value function extracts the value a singlee element. The /text() thing
is not needed, but tends to be more efficient.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

Thank you so much for your help. I'm able to query out some data now. Do you know the best way to query multiple xml files? All of the files have the same file format, except the value.

Here is my current code which only works for 1 single xml file.

declare @x xml = (select xmldata from xmltest where DateCreated = '2020-06-10' )

SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]', 'varchar(20)') AS [CollisionOccurredDate],
Agency.a.value('(Time-AllPages/text())[1]', 'varchar(20)') AS [Time],
Agency.a.value('(MotorVehicleInvolvedWithI-AllPages/text())[1]', 'varchar(20)') AS [MotorVehicleInvolvedWithI],
Agency.a.value('(PedestrianActionA-AllPages/text())[1]', 'varchar(20)') AS [PedestrianAction],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)

I really appreciate your inputs,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thank you so much for your help. I'm able to query out some data now.
Do you know the best way to query multiple xml files? All of the files
have the same file format, except the value.

SELECT ...
FROM tbl
CROSS APPLY tbl.xmlcol.nodes('/agency') AS Agency(A)
CROSS APPLY etc

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

Could you please be more specific on how to read xml data in multiple xml files given all of the xml files have the same structure?

My below query only works for a single xml file, but I need to query the data from a bunch of files.


declare @x xml = (select xmldata from xmltbl)
SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]', 'varchar(20)') AS [CollisionOccurredDate],
Agency.a.value('(Time-AllPages/text())[1]', 'varchar(20)') AS [Time],
Agency.a.value('(MotorVehicleInvolvedWithI-AllPages/text())[1]', 'varchar(20)') AS [MotorVehicleInvolvedWithI],
Agency.a.value('(PedestrianActionA-AllPages/text())[1]', 'varchar(20)') AS [PedestrianAction],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)

I really appreciate your helps/inputs.

Thank you,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris (chris.sjpd@gmail.com) writes:

Could you please be more specific on how to read xml data in multiple
xml files given all of the xml files have the same structure?

My below query only works for a single xml file, but I need to query the
data from a bunch of files.

You need to create a table, and read the files into the table, one by one.
Then you use the syntax I outlined in my previous post.

You should probably add an id or the file name, so that you know which file
the data is coming from.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Here is a sample output where I need to declare my variable.

select id, datecreated, XMLContent from sometable where DateCreated = '2020-06-18' order by 1


id datecreated XMLContent
--- ----------- -------------------
1 2020-06-18 <agency xmlns="".......>
2 2020-06-18 <agency xmlns="".......>

Sometimes we have 2, 3, 5, 10, etc. rows of records.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

Sorry that your knowledge is a million feet above the ground but mine is on the other end :-(

I think about your suggestion but still can't figure out how my cursor & the rest of the codes work.....

I found some suggestions on https://stackoverflow.com/questions/54183550/how-to-read-data-from-multiple-xml-files-in-sql-server

but I can't correlate to the solution. Logically, your suggestion makes a lot of sense, but my skill is just not there. Please help....

Very much appreciated your help,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Sorry that your knowledge is a million feet above the ground but mine is
on the other end :-(

I think about your suggestion but still can't figure out how my cursor &
the rest of the codes work.....

There are two things:

1) Iterate over the XML files and load them into a table. That is probably better done outside SQL Server using SSIS or whatever you fancy. SQL Server
is not the best tool to iterate over a bunch of files in the file system. Or more directly: it is not designed for this and it is awfully akward.

2) One you have the data in the table, you do as I suggested. The same
pattern appears in the StackOverflow post you referred to. Did you actually
try it? Note that the data type of the column in your table should be xml - that makes things a little easier.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

1. All of these xml files are already in a table.

2. The data type of the column in the table is XML. I try the below query but get the error.


DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)
SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)

Errors:
Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The error makes sense because declaration
DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)

would return multiple rows & each row is an XML file like this:

xmldata
=========
<agency xmlns........ /agency>
<agency xmlns........ /agency>
<agency xmlns........ /agency>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris (chris.sjpd@gmail.com) writes:

DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)
SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM @x.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)

So rather than

FROM @x.nodes('/agency') AS Agency(a)

Try

FROM xmltbl
CROSS APPLY xmldata.nodes('/agency') AS Agency(a)

And then the second CROSS APPLY after that.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

When I execute the below sql query, I get the error:

DECLARE @x xml = (select xmldata from xmltbl)
SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM xmltbl
CROSS APPLY xmldata.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)

Errors:
========
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How do I get rid of this error & get the query to return correct output?

Thank you,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris (chris.sjpd@gmail.com) writes:

DECLARE @x xml = (select xmldata from xmltbl)

That statement will only work if there is a single row in the table.
But what are you trying to acheive? You are not using @x in the rest of the script.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

I need to query out the XML data in a table 's column with XML datatype. The "select xmldata from xmltbl where ..." statement will return several rows. Currently I can get the xmldata if the SELECT statement returns a single row, but the SELECT
statement always returns multiple rows. My goal is to get the result set of XML data in all of these rows at one. Please let me know how I can achieve it.

Please keep in mind that all of these rows have to same structure, except the data.

Thank you & sincerely appreciate your help,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I need to query out the XML data in a table 's column with XML datatype.
The "select xmldata from xmltbl where ..." statement will return several rows. Currently I can get the xmldata if the SELECT statement returns a single row, but the SELECT statement always returns multiple rows. My
goal is to get the result set of XML data in all of these rows at one.
Please let me know how I can achieve it.

What happens if you run

SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)') AS [NumberInjured],
Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS AddressFullText
FROM xmltbl
CROSS APPLY xmldata.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)


That is, the same thing except the DECLARE statement which you don't need?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

Thank you & much appreciated your suggestion. I'm able to query out the data using WHERE condition. So far, the result sets look good.

Thank you so much again,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Erland,

When I run the below query, it has no errors but returns no data. Could you please take a look at my query & let me know what I'm missing? Appreciate your helps very much.

SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Vehicle.v.value('(Make/text())[1]', 'varchar(20)') AS [Make],
Myrole.r.value('(TrafficUnitDriverCheck/text())[1]', 'nvarchar(10)') AS [Driver],
Myrole.r.value('(SobrietyDrugPhysicalA/text())[1]', 'varchar(20)') AS [Sobriety_Drug_Physical_A]

FROM xmltbl
CROSS APPLY xmlcontent.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)
CROSS APPLY Agency.a.nodes('TrafficUnit/TrafficUnit_Number/Driver/Vehicle') AS Vehicle(v)
CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r)

Thank you,
Chris

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

chris (chris.sjpd@gmail.com) writes:

When I run the below query, it has no errors but returns no data. Could
you please take a look at my query & let me know what I'm missing?
Appreciate your helps very much.

SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]', 'varchar(20)') AS [AccidentReportNumber],
Vehicle.v.value('(Make/text())[1]', 'varchar(20)') AS [Make],
Myrole.r.value('(TrafficUnitDriverCheck/text())[1]', 'nvarchar(10)') AS [Driver],
Myrole.r.value('(SobrietyDrugPhysicalA/text())[1]', 'varchar(20)') AS [Sobriety_Drug_Physical_A]

FROM xmltbl
CROSS APPLY xmlcontent.nodes('/agency') AS Agency(a)
CROSS APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS Driver(d)
CROSS APPLY Agency.a.nodes('TrafficUnit/TrafficUnit_Number/Driver/Vehicle') AS Vehicle(v)
CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r)

What I can see directly is that you are not missing anything, but you
are having one too many. This line:

CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r)

Should be

CROSS APPLY Agency.a.nodes('TrafficUnit') AS Myrole(r)

I have learnt the hard way that if you have a leading slash in call to nodes
in CROSS APPLY, you don't get anything back.

A general tip with this query is to change CROSS to OUTER for one or more
until you get row back. With OUTER APPLY the rows in the left table are retained. This way, you can figure out which of the CROSS APPLY that
is giving you problems.

Then you will have to take it from there. If I look at your post from
June 11th, it seems that the structure is:

<TrafficUnit>
<TrafficUnit_Number>1</TrafficUnit_Number>
<Driver>
...
</Driver>
<Vehicle>
...
</Vehicle>

But the second CROSS APPLY is written for


<TrafficUnit>
<TrafficUnit_Number>
<Driver>
<Vehicle>
...
</Vechicle>
</Driver>
</TraficUnit_Number>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)