Get specific columns from xml path

Question

I have this query here:

SELECT      
    T.AKSIONERET, 
    sd.Identification_No, sd.Date_Of_Incorp, 
    sd.Ownership_Desc, sd.Ownership_Code, 
    sd.Subject_Name_Code, sd.Subject_Name, 
    sd.Subject_Type_Code, 
    LEFT(sd.Object_Desc, 3999) AS Object_Desc, 
    sd.Subject_Status_Name, sd.Subject_Name, 
    nr2.NACE_Code, nr2.NACE_PARENT, nr2.Description, 
    sd.Subject_Type_Name, sd.Subject_Status_Name, 
    sd.Subject_Type_Name, f.Place_Of_Registration, sd.Object_Desc
FROM            
    NRC_Subject_DEF AS sd 
LEFT JOIN 
    NRC_Nace_REV2 AS nr2 ON nr2.Subject_DEF_Code = sd.Subject_DEF_Code 
LEFT JOIN 
    NRC_ForeignCompany AS f ON sd.ForeignCompany_Code = f.ForeignCompany_Code             
LEFT JOIN 
    (SELECT 
         SDP.Subject_DEF_Code,
         (SELECT 
              SDP1.Subject_DEF_Code, P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc AS FullAksionerCitizenship
          FROM 
              NRC_PeopleAddress P 
          INNER JOIN 
              NRC_SubjectDefPeople SDP1 ON SDP1.People_Code = P.People_Code
          WHERE 
              SDP1.Subject_DEF_Code = SDP.Subject_DEF_Code                         
          GROUP BY 
              SDP1.Subject_DEF_Code,  P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc 
          FOR XML PATH('')) AS AKSIONERET
      FROM 
          NRC_SubjectDefPeople SDP 
      WHERE 
          SDP.Groyp_Type IN (1,2)           
      GROUP BY 
          SDP.Subject_DEF_Code) AS T ON T.Subject_DEF_Code = SD.SUBJECT_DEF_CODE

I need to retrieve, instead of T.AKSIONERET the FullAksionerCitizenship which I get from the for xml path.

I know I have to do some other SELECT but I kept getting errors, syntax errors.

Could someone help?

Thanks in advance

Answer 1

I'm quite sure, that there could be a better design and I'm not able to run this against test data off course, but this could work:

SELECT      
    T.AKSIONERET, 
    T2.FullAksionerCitizenship,
    sd.Identification_No, sd.Date_Of_Incorp, 
    sd.Ownership_Desc, sd.Ownership_Code, 
    sd.Subject_Name_Code, sd.Subject_Name, 
    sd.Subject_Type_Code, 
    LEFT(sd.Object_Desc, 3999) AS Object_Desc, 
    sd.Subject_Status_Name, sd.Subject_Name, 
    nr2.NACE_Code, nr2.NACE_PARENT, nr2.Description, 
    sd.Subject_Type_Name, sd.Subject_Status_Name, 
    sd.Subject_Type_Name, f.Place_Of_Registration, sd.Object_Desc
FROM            
    NRC_Subject_DEF AS sd 
LEFT JOIN 
    NRC_Nace_REV2 AS nr2 ON nr2.Subject_DEF_Code = sd.Subject_DEF_Code 
LEFT JOIN 
    NRC_ForeignCompany AS f ON sd.ForeignCompany_Code = f.ForeignCompany_Code             
LEFT JOIN 
    (SELECT 
         SDP.Subject_DEF_Code,
         (SELECT 
              SDP1.Subject_DEF_Code, P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc AS FullAksionerCitizenship
          FROM 
              NRC_PeopleAddress P 
          INNER JOIN 
              NRC_SubjectDefPeople SDP1 ON SDP1.People_Code = P.People_Code
          WHERE 
              SDP1.Subject_DEF_Code = SDP.Subject_DEF_Code                         
          GROUP BY 
              SDP1.Subject_DEF_Code,  P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc 
          FOR XML PATH('')) AS AKSIONERET
      FROM 
          NRC_SubjectDefPeople SDP 
      WHERE 
          SDP.Groyp_Type IN (1,2)           
      GROUP BY 
          SDP.Subject_DEF_Code) AS T ON T.Subject_DEF_Code = SD.SUBJECT_DEF_CODE
--here starts the new block (difficult for me without knowing your db...)
LEFT JOIN 
    (SELECT 
         (SELECT 
              SDP1.Subject_DEF_Code, P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc AS FullAksionerCitizenship
          FROM 
              NRC_PeopleAddress P 
          INNER JOIN 
              NRC_SubjectDefPeople SDP1 ON SDP1.People_Code = P.People_Code
          WHERE 
              SDP1.Subject_DEF_Code = SDP.Subject_DEF_Code                         
          GROUP BY 
              SDP1.Subject_DEF_Code,  P.First_Name + ' ' + P.Last_Name + '-' + P.Citizenship_Desc 
          ) AS FullAksionerCitizenship
      FROM 
          NRC_SubjectDefPeople SDP 
      WHERE 
          SDP.Groyp_Type IN (1,2)           
      GROUP BY 
          SDP.Subject_DEF_Code) AS T2 ON T2.Subject_DEF_Code = SD.SUBJECT_DEF_CODE