SQL Reading XML child nodes with given value matching with other child node

I am trying to get all Trustee with GPOGroupedAccessEnum for given Identifier. When I try to get all child nodes for given identifier only brings one. I get xml from table xml column. There are multiple GPos and it has identifier Guid and name trustee name and GPOGroupedAccessEnum. I need to list all Trustee names and GPOGroupedAccessEnum for given matching identifier

   <report><GPO xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="fff" xmlns="http://www.microsoft.com/GroupPolicy/Settings">
<Identifier>
  <Identifier xmlns="http://www.microsoft.com/GroupPolicy/Types">{12121212-7C68-4R56-DF44-222222222}</Identifier>
  <Domain xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST.net</Domain>
</Identifier>
<Name>TEST GPO</Name>
<IncludeComments>true</IncludeComments>
<CreatedTime>2017-07-13T14:20:24</CreatedTime>
<ModifiedTime>2018-01-19T14:37:56</ModifiedTime>
<ReadTime>2020-04-13T04:56:44.3177387Z</ReadTime>
<SecurityDescriptor>
  <Owner xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
    <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-3294748472-2709727690-3963727160-512</SID>
    <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST\Domain Admins</Name>
  </Owner>
  <Group xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
    <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-3294748472-2709727690-3963727160-512</SID>
    <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST\Domain Admins</Name>
  </Group>
  <PermissionsPresent xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">true</PermissionsPresent>
  <Permissions xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">
    <InheritsFromParent>false</InheritsFromParent>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-9</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Read</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-3294748472-2709727690-3963727160-519</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST\Enterprise Admins</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Edit, delete, modify security</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-18</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">NT AUTHORITY\SYSTEM</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Edit, delete, modify security</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-3294748472-2709727690-3963727160-11559</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST\MIS Staff</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Apply Group Policy</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-11</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">NT AUTHORITY\Authenticated Users</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Read</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
    <TrusteePermissions>
      <Trustee>
        <SID xmlns="http://www.microsoft.com/GroupPolicy/Types">S-1-5-21-3294748472-2709727690-3963727160-512</SID>
        <Name xmlns="http://www.microsoft.com/GroupPolicy/Types">TEST\Domain Admins</Name>
      </Trustee>
      <Type xsi:type="PermissionType">
        <PermissionType>Allow</PermissionType>
      </Type>
      <Inherited>false</Inherited>
      <Applicability>
        <ToSelf>true</ToSelf>
        <ToDescendantObjects>false</ToDescendantObjects>
        <ToDescendantContainers>true</ToDescendantContainers>
        <ToDirectDescendantsOnly>false</ToDirectDescendantsOnly>
      </Applicability>
      <Standard>
        <GPOGroupedAccessEnum>Edit, delete, modify security</GPOGroupedAccessEnum>
      </Standard>
      <AccessMask>0</AccessMask>
    </TrusteePermissions>
  </Permissions>
  <AuditingPresent xmlns="http://www.microsoft.com/GroupPolicy/Types/Security">false</AuditingPresent>
</SecurityDescriptor>
<FilterDataAvailable>true</FilterDataAvailable>

This is the select statement but it returns only one record.

select  Main.value('(*:SecurityDescriptor/*:Permissions/*:TrusteePermissions/*:Trustee/*:SID)[1]','varchar(100)') as SIDs
    , Main.value('(*:SecurityDescriptor/*:Permissions/*:TrusteePermissions/*:Trustee/*:Name)[1]','varchar(100)') as SecuiyName
    , Main.value('(*:SecurityDescriptor/*:Permissions/*:TrusteePermissions/*:Standard/*:GPOGroupedAccessEnum)[1]','varchar(100)') as permission
from  @XMLwithOpenXML 
    cross apply XMLData.nodes('report/*:GPO') main(Main) 
where
    Main.value('(*:Identifier/*:Identifier)[1]','varchar(100)') = '{12121212-7C68-4R56-DF44-222222222}'

this is the result i get it. it just gets first child node however there are more to be listed.

I ran your XML through a tidy.

I updated the nodes() query to get all TrustedPermissions, but only if the identity matches that 1212 GUID

select  Main.value('(*:Trustee/*:SID)[1]','varchar(100)') as SIDs
    , Main.value('(*:Trustee/*:Name)[1]','varchar(100)') as SecuiyName
    , Main.value('(*:Standard/*:GPOGroupedAccessEnum)[1]','varchar(100)') as permission
from  @xml.nodes('report/*:GPO[*:Identifier/*:Identifier="{12121212-7C68-4R56-DF44-222222222}"]/*:SecurityDescriptor/*:Permissions/*:TrusteePermissions') main(Main)