Forum Discussion
Solved
Using XQUERY on complicated XML
Hi All, and thanks in advance for any help you can provide. I'm currently working on SSMS v20 and am trying to parse XML in SQL Server using XQUERY. I've looked at dozens of examples, tutorials, etc...
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
A table containing all of the data points in the 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
This works perfectly! Thanks so much.
I tried Deepseek, after few adjust, I got simular result
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 VehicleNode.value('@s:id', 'VARCHAR(50)') AS VehicleID, VehicleNode.value('(nc:VehicleColorPrimaryCode)[1]', 'VARCHAR(50)') AS PrimaryColor, VehicleNode.value('(nc:ItemMakeName)[1]', 'VARCHAR(50)') AS Make, VehicleNode.value('(nc:ItemModelName)[1]', 'VARCHAR(50)') AS Model, VehicleNode.value('(nc:ItemModelYearDate)[1]', 'INT') AS ModelYear, VehicleNode.value('(nc:VehicleCMVIndicator)[1]', 'VARCHAR(10)') AS CMVIndicator, PlateNode.value('(nc:IdentificationID)[1]', 'VARCHAR(20)') AS PlateID, PlateNode.value('(nc:IdentificationExpirationDate/nc:Date)[1]', 'DATE') AS ExpirationDate, PlateNode.value('(j:DrivingJurisdictionAuthorityANSID20Code)[1]', 'CHAR(2)') AS Jurisdiction, PlateNode.value('(nc:IdentificationJurisdictionFIPS10-4Code)[1]', 'VARCHAR(10)') AS FIPSCode, VehicleNode.value('(nc:ConveyanceRegistration/dot_ec:VehiclePlateTypeCode)[1]', 'VARCHAR(20)') AS PlateType, VehicleNode.value('(nc:ConveyanceRegistration/dot_ec:VehiclePlateTypeText)[1]', 'VARCHAR(50)') AS PlateTypeText, VehicleNode.value('(dot_ec:VehiclePassenger16PlusIndicator)[1]', 'VARCHAR(10)') AS Passenger16Plus, VehicleNode.value('(dot_ec:HazMatPlacardDisplayedIndicator)[1]', 'VARCHAR(10)') AS HazMatPlacard, VehicleNode.value('(dot_ec:VehicleRegistrationUnknownIndicator)[1]', 'VARCHAR(10)') AS RegistrationUnknown, VehicleNode.value('(nc:InsuranceCarrierName)[1]', 'VARCHAR(100)') AS InsuranceCarrier FROM YourTableName CROSS APPLY XmlDataColumn.nodes('/dcjis:DataExchange/dot_ec:Vehicle') AS T(VehicleNode) CROSS APPLY VehicleNode.nodes('nc:ConveyanceRegistration/nc:ConveyanceRegistrationPlateIdentification') AS P(PlateNode);