Help with Asset Query Please

Hi all,

Hoping for some help with the query below. I would like to show the Product as well as the CINAME see line ", (select rel.RELATIONSHIP +' ' + ci2.CINAME + Product.COMPONENTNAME + CHAR(10) ".

I'm pretty sure the field I need is Product.COMPONENTNAME I havn't been able to work out the required tables and joins. Also not sure how to format the Acquisition Date as dd/mm/yyyy.

select top 50000 ci."CINAME" AS "CI Name"
, "product"."COMPONENTNAME" as "Compname"
, "Departmentdefinition"."DeptName" AS Department
, "Resources"."ACQUISITIONDATE" AS "Acquisition Date"
, CIType_1816.ATTRIBUTE_1251 AS "PUK Code"
, CIType_1816.ATTRIBUTE_1250 AS "SIM Number"
, CIType_1816.ATTRIBUTE_1252 AS "Telstra Account"
, CIType_1816.ATTRIBUTE_1254 AS "Data Pack"
, (select rel.RELATIONSHIP +' ' + ci2.CINAME + Product.COMPONENTNAME + CHAR(10)
from cirelationships as cirel
left join ci as ci2 on ci2.ciid=cirel.ciid2
left join relationshiptype as rel on rel.relationshiptypeid=cirel.relationshiptypeid
where cirel.ciid=ci.ciid for xml path('')) as 'Relationship Details'
FROM "CIType_1816" "citype_1816"

LEFT JOIN "Resources" ON "citype_1816"."CIID"="Resources"."CIID"
LEFT JOIN "ResourceOwner" ON Resources.resourceID=Resourceowner.resourceID
LEFT JOIN "BaseElement" ON "Resources"."CIID"="BaseElement"."CIID"
LEFT JOIN "ci" ON "BaseElement"."CIID"="ci"."CIID"
LEFT JOIN "citype" ON "ci"."CITYPEID"="citype"."TYPEID"
LEFT JOIN "SDOrganization" "cisite" ON "ci"."SITEID"="cisite"."ORG_ID"
LEFT JOIN "Barcodes" ON "Resources"."BARCODEID"="barcodes"."BARCODEID"
LEFT JOIN "Departmentdefinition" ON Resourceowner.deptid=Departmentdefinition.deptid

WHERE (Resources.CIID IS NOT NULL)