SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @xml xml = N'<List>
    <ObjectAttribute displayName="Unique Identity Number" editMode="ReadOnly" name="Uin" namedColumn="true" type="string"/>
    <ObjectAttribute displayName="Enterprise Account Name" editMode="ReadOnly" name="EnterpriseAccountName" namedColumn="true" type="string">
        <AttributeSource name="enterpriseAccountName">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="Active Directory"/>
            </ApplicationRef>
        </AttributeSource>
    </ObjectAttribute>
    <ObjectAttribute displayName="Termination Date" editMode="ReadOnly" name="TerminationDate" namedColumn="true" type="string">
        <AttributeSource name="TERMINATION_DATE">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
        </AttributeSource>
        <AttributeSource name="TerminationDate">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
        </AttributeSource>
    </ObjectAttribute>
    <ObjectAttribute displayName="Country" editMode="ReadOnly" name="Country" namedColumn="true" type="string">
        <AttributeSource name="AppRule: HR APPLICATION 1 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 1 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeSource name="CountryTwoLetter">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
        </AttributeSource>
        <AttributeTargets>
            <AttributeTarget name="c" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Active Directory"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="country" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 1"/>
                </ApplicationRef>
            </AttributeTarget>
        </AttributeTargets>
    </ObjectAttribute>
    <ObjectAttribute displayName="Email" editMode="ReadOnly" name="email" standard="true" type="string">
        <AttributeSource name="AppRule: HR APPLICATION 1 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 1 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeSource name="AppRule: HR APPLICATION 2 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 2 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeTargets>
            <AttributeTarget name="email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 1"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="Email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 2"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="Email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="EmailEffectiveDate" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
                </ApplicationRef>
                <RuleRef>
                    <Reference class="sailpoint.object.Rule" name="HR APPLICATION 2 EmailEffectiveDate TargetTransformation Rule"/>
                </RuleRef>
            </AttributeTarget>
        </AttributeTargets>
    </ObjectAttribute>
</List>
';

SELECT
  Displayname = x.attr.value('@displayName', 'varchar(100)'),
  editMode = x.attr.value('@editMode', 'varchar(100)'),
  name = x.attr.value('@name', 'varchar(100)'),
  namedColumn = '???',
  type = x.attr.value('@type', 'varchar(100)'),
  SourceName = x.attr.query('for $i in AttributeSource/@name return concat(./@name, "&#xa;")').value('text()[1]','nvarchar(1000)'),
  [Source App(s)] = x.attr.query('for $i in AttributeSource/*/Reference/@name return concat(@name, "&#xa;")').value('text()[1]','nvarchar(max)'),
  [Target App(s)] = x.attr.query('for $i in
      ( for $t in AttributeTargets/AttributeTarget
        return ($t/@name, $t/*/Reference/@name)
      )
      return concat(./@name, "&#xa;")
      ').value('text()[1]','nvarchar(max)')
FROM @xml.nodes('List/ObjectAttribute') x(attr)

Displayname	editMode	name	namedColumn	type	SourceName	Source App(s)	Target App(s)
Unique Identity Number	ReadOnly	Uin	???	string	null	null	null
Enterprise Account Name	ReadOnly	EnterpriseAccountName	???	string	EnterpriseAccountName	EnterpriseAccountName	null
Termination Date	ReadOnly	TerminationDate	???	string	TerminationDate TerminationDate	TerminationDate TerminationDate	null
Country	ReadOnly	Country	???	string	Country Country	Country Country Country	Country Country Country Country
Email	ReadOnly	email	???	string	email email	email email email email	email email email email email email email email email