Forum Discussion

0105's avatar
0105
Copper Contributor
Jan 24, 2025
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., but every one has such simplistic XML that I can't figure out how to do it on my more complicated (more levels) XML. Here is a sample of data in an XML column:

<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:Vehicle s:id="vehicle1">
    <nc:VehicleColorPrimaryCode>BLU</nc:VehicleColorPrimaryCode>
    <nc:ItemMakeName>VOLK</nc:ItemMakeName>
    <nc:ItemModelName>JETTA</nc:ItemModelName>
    <nc:ItemModelYearDate>2011</nc:ItemModelYearDate>
    <nc:VehicleCMVIndicator>false</nc:VehicleCMVIndicator>
    <nc:ConveyanceRegistration>
      <nc:ConveyanceRegistrationPlateIdentification>
        <nc:IdentificationID>194JM4</nc:IdentificationID>
        <nc:IdentificationExpirationDate>
          <nc:Date>2020-04-30</nc:Date>
        </nc:IdentificationExpirationDate>
        <j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code>
        <nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code>
      </nc:ConveyanceRegistrationPlateIdentification>
      <dot-ec:VehiclePlateTypeCode>PAN</dot-ec:VehiclePlateTypeCode>
      <dot-ec:VehiclePlateTypeText>Passenger Normal</dot-ec:VehiclePlateTypeText>
    </nc:ConveyanceRegistration>
    <dot-ec:VehiclePassenger16PlusIndicator>false</dot-ec:VehiclePassenger16PlusIndicator>
    <dot-ec:HazMatPlacardDisplayedIndicator>false</dot-ec:HazMatPlacardDisplayedIndicator>
    <dot-ec:VehicleRegistrationUnknownIndicator>false</dot-ec:VehicleRegistrationUnknownIndicator>
    <nc:InsuranceCarrierName>GOVT EMPLOYEE INS</nc:InsuranceCarrierName>
  </dot-ec:Vehicle>
</dcjis:DataExchange>

 

Thanks.

  • rodgerkong's avatar
    rodgerkong
    Feb 06, 2025

    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

     

  • 0105's avatar
    0105
    Copper Contributor
    Feb 07, 2025

    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.

     

    • rodgerkong's avatar
      rodgerkong
      Iron Contributor
      Feb 08, 2025
      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;

       

      • 0105's avatar
        0105
        Copper Contributor
        Feb 10, 2025

        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);

    • 0105's avatar
      0105
      Copper Contributor
      Feb 03, 2025

      A table containing all of the data points in the XML.

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Feb 06, 2025

        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

         

Resources