Home › Database › sql › SQL Server XML example

SQL Server XML example

Posted on Posted in sql, SQLServer Tagged with , ,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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;