Xml To Sql - Selecting Multiple Nodes With The Same Name

I have working code that can select a few values from an XML file. The problem is that I have multiple nodes with the same name. Here is a snippet of the XML:

Solution 1:

Depends what you want to do... If you know there are exactly 2 "Address_Components" that you want to grab, you can modify your query like so:

;WITH XMLNAMESPACES(DEFAULT'urn:com.workday.report/Countries_and_Their_Address_Components_Summary')
    select 
        xmldata.[ISO], xmldata.[Component 1], xmldata.[Component 2], xmldata.[Required]
    from@inputxmlcross apply (
        select 
            [ISO] = xmldata.value('(Country/ID)[3]', 'VARCHAR(MAX)'),
            [Component 1] = xmldata.value('(Address_Components/Address_Component/ID)[2]', 'VARCHAR(MAX)'),
            [Component 2] = xmldata.value('(Address_Components[2]/Address_Component/ID)[2]', 'VARCHAR(MAX)'),
            [Required] = xmldata.value('(Address_Components/Required)[1]', 'INT')
        from x.nodes('/Report_Data/Report_Entry') Z1(xmldata)
    ) xmldata

And the results look like this:

ISO   Component 1               Component 2               Required
----- ------------------------- ------------------------- -----------
AFG   ADDRESS_LINE_1_LOCAL      ADDRESS_LINE_2_LOCAL      0

However, if there can be any number of "Address_Components", and you want to grab them into separate records, you can rewrite your query like this:

;WITH XMLNAMESPACES(DEFAULT'urn:com.workday.report/Countries_and_Their_Address_Components_Summary')
    select 
        [ISO] = Report_Entry.x.value('(Country/ID)[3]', 'VARCHAR(MAX)')
        , [Component] = Address_Components.x.value('(Address_Component/ID)[2]', 'VARCHAR(MAX)')
        , [Required] = Address_Components.x.value('(Required)[1]', 'INT')
    from@inputxmlcross apply x.nodes('/Report_Data/Report_Entry') Report_Entry(x)
    cross apply Report_Entry.x.nodes('./Address_Components') Address_Components (x)

And the results look like this:

ISO   Component                 Required
----- ------------------------- -----------
AFG   ADDRESS_LINE_1_LOCAL      0
AFG   ADDRESS_LINE_2_LOCAL      0

Share