SQL Server XML example

WITH XMLNAMESPACES('http://www.fineos.com/ta/common/external' AS ns2),
myCte as
(
   select
      top 121 i,
      cast(cast(packeddata as varchar(MAX)) AS XML) packeddata
   from
      TOCExtraData
   where
      I_OCDOCTYP_DOCUMENTS = '37'
),
myNewCTE as
(
   SELECT
      i as COMPLAINT_ID,
      t.packeddata.value('name[1]', 'varchar(50)') as name,
      t.packeddata.value('prompt[1]', 'varchar(100)') as prompt,
      t.packeddata.value('value[1]', 'varchar(800)') as value,
      'STRING' as format
   from
      myCte
   CROSS APPLY
      packeddata.nodes('/ns2:DataSet/StringObject') as t(packeddata)

   UNION ALL

   SELECT
      i,
      t.packeddata.value('name[1]', 'varchar(50)') as name,
      t.packeddata.value('prompt[1]', 'varchar(100)') as prompt,
      t.packeddata.value('value[1]', 'varchar(800)') as value,
      'DATE'
   from
      myCte
   CROSS APPLY
      packeddata.nodes('/ns2:DataSet/DateTimeObject') as t(packeddata)
where
   i = 99074
)

select * from mynewcte;

select
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Complaint_Against') as COMPLAINT_AGAINST,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Complaint_Type') as COMPLAINT_CATEGORY,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Outcome_Investigation') as COMPLAINT_SUB_CATEGORY,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Complaint_Lodged_by') as COMPLAINT_LODGED_BY,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Date_Received') as COMPLAINT_RECEIVED_DATE,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Date_Resolved') as COMPLAINT_RESOLVED_DATE,
   (select value from myNewCTE m where m.COMPLAINT_ID = e.i and name = 'Complainant_Satisfied') as COMPLAINANT_SATISFIED,
   di.name as COMPLAINT_STATUS
from
   TOCExtraData e, TODomainInstance di
where
   e.Status = di.i
   and I_OCDOCTYP_DOCUMENTS = '37' /*and  e.i = 99074*/ order by 1;