How to group one to many data in one XML node - SQL Server -

there 1 many relation between tables, 1 inspector can have many documents , group them inspector id , show them in 1 xml <row> node.

what have tried far here;

sql:

select      d.inspectorid  "@inspectorid",      d.docid  "qualification/docid",      d.filesize "qualification/filesize",      q.name "qualification/name",      d.enddate "qualification/enddate",      d.datecreated "qualification/datecreated"      inspectordocs d left join      documenttype dt on dt.documenttypeid = d.doctype left join      qualificationtype q on q.qualificationtypeid =  d.qualificationtypeid       d.inspectorid = 6390  xml path 

xml output:

<row inspectorid="6390">     <qualification>         <docid>23107</docid>         <filesize>248724</filesize>         <name>aws cwi</name>         <enddate>2016-12-01t00:00:00</enddate>         <datecreated>2014-07-23t21:30:00</datecreated>     </qualification> </row> <row inspectorid="6390">     <qualification>         <docid>23108</docid>         <filesize>524934</filesize>         <name>other</name>         <enddate>1900-12-12t00:00:00</enddate>         <datecreated>2014-07-23t21:31:00</datecreated>     </qualification> </row> 

how can achieve desired output:

<row inspectorid="6390">     <qualification>           <docid>23107</docid>           <filesize>248724</filesize>           <name>aws cwi</name>           <enddate>2016-12-01t00:00:00</enddate>           <datecreated>2014-07-23t21:30:00</datecreated>     </qualification>     <qualification>           <docid>23108</docid>           <filesize>524934</filesize>           <name>other</name>           <enddate>1900-12-12t00:00:00</enddate>           <datecreated>2014-07-23t21:31:00</datecreated>     </qualification>  </row> 

try this:

select  d1.inspectorid "@inspectorid"       , ( select    d.docid "qualification/docid"                   , d.filesize "qualification/filesize"                   , q.name "qualification/name"                   , d.enddate "qualification/enddate"                   , d.datecreated "qualification/datecreated"                inspectordocs d                     left join documenttype dt on dt.documenttypeid = d.doctype                     left join qualificationtype q on q.qualificationtypeid = d.qualificationtypeid               d.inspectorid = d1.inspectorid                   xml path             , type         )    ( select distinct                     inspectorid                inspectordocs         ) d1   d1.inspectorid = 6390     xml path