Using XQUERY on complicated XML

Is this what you want?
Replace the table name (#test) to your table that contain xml column, and replace column name(xml_data) to your xml column name, then you can try to run the script.

WITH XMLNAMESPACES(
 'http://www.xfact.com/schemas/eopss/dcjis/1.0' AS dcjis
,'http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension' AS dot_ec
,'http://niem.gov/niem/niem-core/2.0' AS nc
,'http://niem.gov/niem/domains/jxdm/4.0' AS j
,'http://niem.gov/niem/structures/2.0' AS s)

SELECT 
t.id, --assume your table has a key named id. You can replace it with your key column or just comment this line
 VehicleNode.value(N'(@s:id)', 'nvarchar(20)') as V_ID  
,VehicleNode.value(N'(nc:VehicleColorPrimaryCode)[1]', 'nvarchar(20)') as ColorPrimaryCode
,VehicleNode.value(N'(nc:ItemMakeName)[1]', 'nvarchar(20)') as MakeName
,VehicleNode.value(N'(nc:ItemModelName)[1]', 'nvarchar(20)') as ModelName
,VehicleNode.value(N'(nc:ItemModelYearDate)[1]', 'nvarchar(4)') as ModelYear
,VehicleNode.value(N'(nc:VehicleCMVIndicator)[1]', 'nvarchar(10)') as CMVIndicator
,RegNode.value(N'(nc:ConveyanceRegistrationPlateIdentification/nc:IdentificationID)[1]','nvarchar(20)') as ConveyanceRegistrationPlateIdentification
,RegNode.value(N'(nc:ConveyanceRegistrationPlateIdentification/nc:IdentificationExpirationDate/nc:Date)[1]','date') as ConveyanceRegistrationExpirationDate
,RegNode.value(N'(nc:ConveyanceRegistrationPlateIdentification/j:DrivingJurisdictionAuthorityANSID20Code)[1]','nvarchar(10)') as DrivingJurisdictionAuthorityANSID20Code
,RegNode.value(N'(nc:ConveyanceRegistrationPlateIdentification/nc:IdentificationJurisdictionFIPS10-4Code)[1]','nvarchar(10)') as IdentificationJurisdictionFIPS10_4Code
,RegNode.value(N'(dot_ec:VehiclePlateTypeCode)[1]','nvarchar(10)') as VehiclePlateTypeCode
,RegNode.value(N'(dot_ec:VehiclePlateTypeText)[1]','nvarchar(10)') as VehiclePlateTypeText
,VehicleNode.value(N'(dot_ec:VehiclePassenger16PlusIndicator)[1]' ,'nvarchar(10)') as VehiclePassenger16PlusIndicator
,VehicleNode.value(N'(dot_ec:HazMatPlacardDisplayedIndicator)[1]', 'nvarchar(10)') as HazMatPlacardDisplayedIndicator
,VehicleNode.value(N'(dot_ec:VehicleRegistrationUnknownIndicator)[1]', 'nvarchar(10)') as VehicleRegistrationUnknownIndicator
,VehicleNode.value(N'(nc:InsuranceCarrierName)[1]', 'nvarchar(20)') as InsuranceCarrierName
FROM #test t  --Replace "#test" with your table name
CROSS APPLY t.xml_data.nodes('/dcjis:DataExchange/dot_ec:Vehicle') AS V(VehicleNode) --Replace "xml_data" with your xml column name
CROSS APPLY VehicleNode.nodes('nc:ConveyanceRegistration') AS R(RegNode)
WHERE xml_data is not null; --Replace "xml_data" with your xml column name