Forum Discussion
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
I also have this XML which is structured differently than the others, and I can't get XQUERY to work:
<dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0">
<dcjis-ext:DataElements>
<dcjis-ext:DataElement dcjis-ext:elementName="citationNo">039849AA</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="citationDate">2017-12-17</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="resultOfStopCode">P</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="resultOfStopText">CMVI Payable</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="violatorTypeCode">1</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="violatorTypeText">Operator</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="citationVoidedInd">false</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="plateReaderInd">false</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="licenseStateCode">MA</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="licenseStateText">Massachusetts, USA</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="birthYear">1956</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="sexCode">F</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="sexText">FEMALE</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="raceCode">W</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="raceText">WHITE</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateStateCode">MA</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateStateText">Massachusetts, USA</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateTypeCode">PAN</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="vehiclePlateTypeText">PASSENGER NORMAL</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="locationCityCode">004</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="locationCityText">Worcester</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="locationRoadwayTypeCode">1</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="locationRoadwayTypeText">Secondary Roadway</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="searchInd">false</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingAgencyCode">C6</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingAgencyText">State Police Troop C-6</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerFirstName">RONALD</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerMiddleName">Q</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerLastName">WILson</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="issuingOfficerIdNo">3732</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="activityCode">P</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="activityText">Patrol</dcjis-ext:DataElement>
<dcjis-ext:DataElement dcjis-ext:elementName="verbalWarningInd">false</dcjis-ext:DataElement>
</dcjis-ext:DataElements>
</dcjis:DataExchange>
Any help would be greatly appreciated.
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 dcjis_ext ,'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 ) SELECT t.id ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="citationNo"][1])', 'nvarchar(20)') as citationNo ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="citationDate"][1])', 'date') as citationDate ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="resultOfStopCode"][1])', 'nvarchar(2)') as resultOfStopCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="resultOfStopText"][1])', 'nvarchar(20)') as resultOfStopText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="violatorTypeCode"][1])', 'nvarchar(2)') as violatorTypeCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="violatorTypeText"][1])', 'nvarchar(20)') as violatorTypeText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="citationVoidedInd"][1])', 'nvarchar(10)') as citationVoidedInd ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="plateReaderInd"][1])', 'nvarchar(10)') as plateReaderInd ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="licenseStateCode"][1])', 'nvarchar(2)') as licenseStateCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="licenseStateText"][1])', 'nvarchar(20)') as licenseStateText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="birthYear"][1])', 'nvarchar(4)') as birthYear ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="sexCode"][1])', 'nvarchar(2)') as sexCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="sexText"][1])', 'nvarchar(20)') as sexText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="raceCode"][1])', 'nvarchar(2)') as raceCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="raceText"][1])', 'nvarchar(20)') as raceText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="vehiclePlateStateCode"][1])', 'nvarchar(2)') as vehiclePlateStateCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="vehiclePlateStateText"][1])', 'nvarchar(20)') as vehiclePlateStateText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="vehiclePlateTypeCode"][1])', 'nvarchar(2)') as vehiclePlateTypeCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="vehiclePlateTypeText"][1])', 'nvarchar(20)') as vehiclePlateTypeText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="locationCityCode"][1])', 'nvarchar(4)') as locationCityCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="locationCityText"][1])', 'nvarchar(20)') as locationCityText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="locationRoadwayTypeCode"][1])', 'nvarchar(2)') as locationRoadwayTypeCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="locationRoadwayTypeText"][1])', 'nvarchar(20)') as locationRoadwayTypeText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="searchInd"][1])', 'nvarchar(10)') as searchInd ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingAgencyCode"][1])', 'nvarchar(2)') as issuingAgencyCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingAgencyText"][1])', 'nvarchar(20)') as issuingAgencyText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingOfficerFirstName"][1])', 'nvarchar(50)') as issuingOfficerFirstName ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingOfficerMiddleName"][1])', 'nvarchar(50)') as issuingOfficerMiddleName ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingOfficerLastName"][1])', 'nvarchar(50)') as issuingOfficerLastName ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="issuingOfficerIdNo"][1])', 'nvarchar(10)') as issuingOfficerIdNo ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="activityCode"][1])', 'nvarchar(2)') as activityCode ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="activityText"][1])', 'nvarchar(20)') as activityText ,DataNode.value(N'(dcjis_ext:DataElement[@dcjis_ext:elementName="verbalWarningInd"][1])', 'nvarchar(10)') as verbalWarningInd FROM #test t CROSS APPLY t.xml_data.nodes('/dcjis:DataExchange/dcjis_ext:DataElements') AS V(DataNode) WHERE xml_data is not null;Great! Thanks so much.
I'm still having troubles with another bit of XML. I can't seem to get my CROSS APPLYs to work with the
id within the tag). The XML and my SQL code are below.
<dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0" xmlns:s="http://niem.gov/niem/structures/2.0">
<dot-ec:Person s:id="person1">
<nc:PersonName>
<nc:PersonGivenName>MATHIAS</nc:PersonGivenName>
<nc:PersonMiddleName>F</nc:PersonMiddleName>
<nc:PersonSurName>WEMBLEY</nc:PersonSurName>
</nc:PersonName>
</dot-ec:Person>
</dcjis:DataExchange>SQL:
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 [z_06].[MSPPSDataId]
, oNode.value(N'(nc:PersonGivenName)[1]', 'NVARCHAR(50)')
AS OfficerFirstName
, oNode.value(N'(nc:PersonMiddleName)[1]', 'NVARCHAR(50)')
AS OfficerMiddleName
, LEFT(oNode.value(N'(nc:PersonMiddleName)[1]', 1), 'NCHAR(1)')
AS OfficerMI
, oNode.value(N'(nc:PersonSurnameName)[1]', 'NVARCHAR(50)')
AS OfficerLastName
INTO [dbo].[z_06_Officer_Data]
FROM [dbo].[z_06_Officer_XML] AS [z_06]
CROSS APPLY [z_06].[Officer].nodes('/dcjis:DataExchange/dot-ec:Person') AS o(oNode)
CROSS APPLY oNode.nodes('nc:PersonName') AS p(pNode);s:id in node <dot-ec:Person> is a xml attribute, you should use "@" in XQuery to filter its value, like "@s:id". Check the first answer I posted.